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 in each class AS Students
count of students where statusID = 1 As Actives
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.
精彩评论