SQL query - multiple COUNT on same column with different values from nested SELECT query

Question

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 and Absent 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.

Answer 1

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