samedi 25 avril 2015

How to write a query with count


I have two tables as follows:

==================
StudentsClasses  
----------------
ID (Registration ID of the class)
StudentID (ID of student taking class)
ClassID (ID of certain class)
----------------
==================

Students
---------------
ID (ID of student)
Name (Name of student)
GradeLevelID (Grade of student)
---------------
==================

And they are joined by StudentsClasses.StudentID and Students.ID.

I am trying to write a query to return the students with the least classes registered. My query is:

SELECT Students.Name, COUNT(StudentsClasses.StudentID) AS Expr1
FROM     StudentsClasses INNER JOIN
                  Students ON StudentsClasses.StudentID = Students.ID
GROUP BY StudentsClasses.StudentID, Students.Name
ORDER BY Expr1

However, that only returns all the students with at least 1 class in ASC order.

I know the correct answer is 7 students with 0 classes.

How can I modify my query to return only those 7 students with 0 classes.


Aucun commentaire:

Enregistrer un commentaire