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";
WHERE Student.StartDate >= DATEADD(month, -1, GETDATE());
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
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.