开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜