SQL query - multiple COUNT on same column with different values from nested SELECT query
SELECT StudentID,
SUM(case when Unapproved =1 then 1 else 0 end) as Late,
SUM(case when Unapproved =2 then 1 else 0 end) as Absent
from results where
StudentID in (SELECT studentid FROM [Results] where StudentYearLevel='10' and Date > 20130101)
group by StudentID
MarkFromAus
Updated on June 09, 2022Comments
-
MarkFromAus over 1 year
I'm struggling with the correct SQL syntax to return counts of a particular value in one column.
This query works (probably incorrect syntax, but SQL Server 2008 seems happy)
SELECT StudentID, count(UnApproved)as Late, count(Unapproved) as Absent from results WHERE unapproved=1 and StudentID in ( SELECT studentid FROM [Results] WHERE StudentYearLevel='10' and Date > 20130101) group by StudentID )
Of course, both
Late
andAbsent
columns return the same values because of where the 'where' is.So what this is doing is (from the right) determining the IDs of students who are members of "Year 10".
Then, for each student ID returned, I need it to return the count of unapproved absences recorded where the type of unapproved absence is 1 and in the next column, also return the count of unapproved absences where the type is 2.
If I try to submit the query like so:-
SELECT StudentID, count(UnApproved)as Late where unapproved=2, count(Unapproved) as Absent from results where unapproved=1 and StudentID in ( SELECT studentid FROM [Results] where StudentYearLevel='10' and Date > 20130101 ) group by StudentID
SQL Server cracks it, and underlines almost the entire query in red.
I need to end up these three columns:-
StudentID | Late | Absent
And the three columns having student IDs with the appropriate counts.
I can do most basic select queries, but when it comes to nested queries, unions, joins, inners I'm out of my depth. Any help would be most appreciated. By no means am I sure my (working) query is in any way correctly structured, 'coz I'm a hack at this.