Geek Answers Handbook

SQL query - multiple COUNTs in one column with different values ​​from a nested SELECT query

I am struggling with the correct SQL syntax to return counters of a specific value in a single column.

This query works (possibly the wrong 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, the Late and Absent columns return the same value due to where "where" is.

So this is done (on the right) by defining the identifiers of students who are members of Year 10.

Then, for each student ID returned, I need to return the score of the unapproved paragraphs recorded where the type of the unapproved absence is 1 and in the next column also return the number of unapproved paragraphs where the type is 2.

If I try to send a request like this: -

 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 pops it and underlines almost the entire query in red.

I need to finish these three columns: -

StudentID | Late | Absent

And three columns with student IDs with corresponding values.

I can make most of the basic queries of choice, but when it comes to nested queries, unions, unions, I'm not deep inside. Any help would be greatly appreciated. By no means I'm not sure that my (working) request is in any way properly structured, "coz I hacked this.

+6
source share
1 answer
 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
+21
source

More articles:


All Articles