How to get count of different values for one field

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

How to get count of different values for one field

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2013-05-07 : 12:41:07

I'm trying to get separate counts for the same field in one query, where each count is when the field is equal to a specific value.

Here are two example data tables...


Teachers
ID    Name
1     Sam
2     Pat
3     Richard

Courses
ID    Subject   TeacherID
1      21          1
2      21          2
3      22          2
4      23          3
5      21          3
6      21          3

Now, I want to count the # of courses per subjects 21 and 23 for each teacher.

So, I want to have two counts in my select statement, and a where clause for them. Something like:


select (count(subject) where subject = '21'), (count(subject) where subject='23'), teacherID

Is this possible?

Thanks.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-05-07 : 12:46:09

quote:
Now, I want to count the # of courses per subjects 21 and 23 for each teacher.

yup it is possible using the Group By clause.

SELECT Column1,Column2,Count(Column3)
FROM TableName
GROUP BY Column1,Column2

Cheers
MIK

crugerenator
Posting Yak Master

126 Posts

Posted - 2013-05-07 : 13:01:32

Hmm. I don't think this would work since I need to count the # of rows set to 21, and count the # of rows set to 23. So I would need 2 separate counts.

crugerenator
Posting Yak Master

126 Posts

Posted - 2013-05-07 : 13:16:12

figured it out...


select
sum(case when subject = '21' then 1 else 0 end) as count_21,
sum(case when subject= '23' then 1 else 0 end) as count_23

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2013-05-07 : 13:59:24

SELECT subject, count(*) FROM yourtable GROUP BY subject;

This gives you 2 rows (based on your sample data) - one for subject 21 and one for subject 23.

SELECT t.Name, c.Subject, count(*)
FROM Teachers t
INNER JOIN Courses c ON c.TeacherID = t.ID
GROUP BY t.Name, c.Subject;

This gives you totals by each Teacher and Subject - with a separate row for each total.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources