How to count all subquery results that returns only the most recent item?

Question

In this post (Adding a Query to a Subquery then produces no results) @D-Shih provided a great solution, which I would like to extend.

How do I add to the results returned, the count of reports by that teacher, even if the subquery is only finding the last one?

I'm trying to solve the <???> AS CountOfReports, line below, but my SQL skills are not that great.

SELECT 
    t.NAME,
    t1.REPORTINGTYPE,
     <???> AS CountOfReports,     <<<< ****
    t1.REPORTINGPERIOD 
FROM 
    teachers AS t 
INNER JOIN
    (SELECT 
         *,
         (SELECT COUNT(*) FROM REPORTS tt 
          WHERE tt.TEACHER_ID = t1.TEACHER_ID 
            AND tt.REPORTINGPERIOD >= t1.REPORTINGPERIOD) rn
     FROM 
         REPORTS t1) AS t1 ON t1.TEACHER_ID = t.id AND rn = 1
ORDER BY 
    t.NAME

score 1 · Accepted Answer · answered Aug 15 '18 at 10:38

You can compute the count with a correlated subquery:

SELECT t.Name,
       r.ReportingType,
       max(r.ReportingPeriod),
       (SELECT count(*)
        FROM Reports r2
        WHERE r2.Teacher_ID = r.Teacher_ID
       ) AS Reports
FROM Teachers t
JOIN Reports r ON t.ID = r.Teacher_ID
GROUP BY r.Teacher_ID;

NAME            REPORTINGTYPE  max(r.ReportingPeriod)  Reports   
--------------  -------------  ----------------------  ----------
Mr John Smith   Final          2017-03                 3         
Ms Janet Smith  Draft          2018-07                 2

How to count all subquery results that returns only the most recent item?

1 Answers1