Easy SQL Group-By question. (I'm new)

I am new to SQL so hopefully this has an easy answer.

I have a Students table (studentID, name, statusID) and a StudentsClasses table (studentID, classID). I've been asked to create a view from the above tables that returns the following columns:

classID: (group-by)

count of students where statusID = 2 As Inactives

I can开发者_StackOverflow社区 easily group-by the classID colunm and count the number of students, but i dont think the count function can be told to count only certain rows. As soon as I add where or Having clauses I lose records that I need to be counted in other columns. Do I need to write a couple of different queries and then join the results of each?

Thanks for any help you can provide!

-David

This is ANSI SQL (should work as long as your SQL implementation is ANSI compatible):

SELECT 
 classID,
 COUNT(*) AS "Students",
 SUM(CASE statusID WHEN 1 THEN 1 ELSE 0 END) AS "Actives",
 SUM(CASE statusID WHEN 2 THEN 1 ELSE 0 END) AS "Inactives",
FROM StudentsClasses class
INNER JOIN Students stud ON class.studentID = stud.studentID
GROUP BY classID

Instead of counting, why don't you try summing a couple of IIF/CASE statements? That is, using IIF, your query would be:

SELECT ClassId, COUNT(*) AS students,
       SUM(IIF(StatusId = 1, 1, 0)) as Actives,
       SUM(IIF(StatusId = 2, 1, 0)) as Inactives
FROM StudentsClasses
INNER JOIN Students ON StudentsClasses.StudentID = Students.StudentID
GROUP BY ClassId

A statement like this will give you a row for each class with a class ID and the number of students in it.

SELECT classID, COUNT(*) AS studentsInClass
    FROM students
    WHERE classID
    IN (SELECT DISTINCT classID FROM students)
    GROUP BY classID

The WHERE x IN (...) thing is something I just learned.

I'm not really sure about the other part. Do you want this all in one table? You could UNION it in, but it would be messy since the data isn't the same.