CODEWITHSUNDEEP
sqlasp.netsql-server
elfico
elfico

Reputation: 550

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

Upvotes: 2

Views: 727

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

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.

Upvotes: 4

Munna Extreme
Munna Extreme

Reputation: 390

I guess this helps

   myConnect.Open();
        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, " +
        "(SELECT Count(stu.StartDate) FROM Students stu WHERE School.SchoolID = stu.SchoolID AND stu.StartDate >= DATEADD(month,-1, GETDATE())) AS CountStartDate" + // your count
        "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";

Upvotes: 0

Abdul Rasheed
Abdul Rasheed

Reputation: 6729

Using CASE WHEN , you can solve this,

SELECT  Count(DISTINCT Courses.CourseID)    AS  CourseCount, 
        Count(DISTINCT Students.StudentID)  AS  StudentCount, 
        Count(Students.StartDate) AS StartCount, 
        Sum(CASE WHEN Student.StartDate >= DATEADD(month,-1, GETDATE()) 
                THEN 1 ELSE 0 END)  AS  StartDateCount ,
        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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270623

You can do this with conditional aggregation. Just add this to the SELECT:

SUM(CASE WHEN Student.StartDate >= DATEADD(month,-1, GETDATE()) THEN 1 ELSE 0 END) as RecentStudents

Upvotes: 0

Related Questions