selecting multiple counts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

selecting multiple counts

Author

Topic

seekaye
Starting Member

6 Posts

Posted - 2011-04-24 : 05:23:03

I have a table something like this

ID Surname Sub T A C
12 Smither Mat A - A
12 Smither ICT B B -
12 Smither Eng C C C
14 Willson His D D D
14 Willson Geo C D D
14 Willson Sci C A B
14 Willson Mat - B C

I want to produce a table of users, their surname and how many subject they study. I achieved this with

SELECT distinct PupilID, PupilSurname, pupilforename,
count(SubjectID) as "No. Subjects" from classlists
GROUP BY PupilID, pupilsurname, pupilforename
ORDER BY pupilsurname, pupilforename

I'd also like a column which counts missing (null) values in the t column (represented by -)

I achieved this using

SELECT distinct PupilID, PupilSurname, pupilforename,
count(SubjectID) as "Missing Targets" from classlists where t is null
GROUP BY PupilID, pupilsurname, pupilforename
ORDER BY pupilsurname, pupilforename

Now I'd like to put both of these together, along with missing A and missing C

surname, forename, subject count, missing T, missing A, missing C

but i'm stuck

thanks for any help

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-24 : 06:39:33

select PupilID, Surname,
subjects = = count(*) ,
MissingTargets = sum(case when T is null then 1 else 0 end) ,
missingA = sum(case when A is null then 1 else 0 end)
from classlists
group by PupilID, Surname

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

seekaye
Starting Member

6 Posts

Posted - 2011-04-24 : 07:42:48

Many thanks, the sum(case.... idea worked a treat

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources