elfico elfico - 6 months ago 10
SQL Question

Using COUNT Function in Table Joins

I am writing a sql code for a report page that that joins three tables. Here is the query I have written.

comm.CommandText = "SELECT Count(DISTINCT Courses.CourseID) AS CourseCount, Count(DISTINCT Students.StudentID) AS StudentCount, Count(Students.StartDate) AS StartCount, School.Name, School.StartDate, School.SchoolFees " +
"FROM Schools " +
"LEFT JOIN Courses ON (School.SchoolID = Courses.SchoolId) " +
"LEFT JOIN Students ON (School.SchoolID = Student.SchoolID) " +
"WHERE School.Active = 1 " +
"GROUP BY School.Name, School.StartDate, School.SchoolFees";


The above query works well. But I want to show the count of the Student.StartDate for each School where Student.StartDate satisfy a condition. Here is the query I want to use

SELECT Count(Students.StartDate)
FROM Students
WHERE Student.StartDate >= DATEADD(month, -1, GETDATE());


I want the above query to be return as part of my main query but dont know how to achieve it. Any help will be appreciated. Thanks

Answer

When you want aggregates from different tables, you should not join the tables and then aggregate, but always build the aggregates first and join these instead. In your case you were able to avoid issues by counting distinct IDs, but that is not always possible (i.e. when looking for sums or avarages). You can count conditionally with CASE WHEN.

SELECT 
  COALESCE(c.CourseCount, 0) AS CourseCount,
  COALESCE(s.StudentCount, 0) AS StudentCount,
  COALESCE(s.StartCount, 0) AS StartCount,
  School.Name, 
  School.StartDate, 
  School.SchoolFees
FROM Schools
LEFT JOIN
(
  SELECT SchoolID, COUNT(*) AS CourseCount
  FROM Courses 
  GROUP BY SchoolID
) c ON c.SchoolId = School.SchoolID
LEFT JOIN
(
  SELECT 
    SchoolID, 
    COUNT(*) AS StudentCount,
    COUNT(CASE WHEN StartDate >= DATEADD(month, -1, GETDATE() THEN 1 END) as StartCount
  FROM Students 
  GROUP BY SchoolID
) s ON s.SchoolId = School.SchoolID
WHERE School.Active = 1;

In case it is guaranteed for every school to have at least one student and one course (which is probably the case), you can change the outer joins to inner joins and get thus rid of the COALESCE expressions.