SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get count of different values for one field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

crugerenator
Posting Yak Master

126 Posts

Posted - 05/07/2013 :  12:41:07  Show Profile  Send crugerenator an AOL message  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 05/07/2013 :  12:46:09  Show Profile  Reply with Quote
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

Edited by - MIK_2008 on 05/07/2013 12:47:06
Go to Top of Page

crugerenator
Posting Yak Master

126 Posts

Posted - 05/07/2013 :  13:01:32  Show Profile  Send crugerenator an AOL message  Reply with Quote
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.
Go to Top of Page

crugerenator
Posting Yak Master

126 Posts

Posted - 05/07/2013 :  13:16:12  Show Profile  Send crugerenator an AOL message  Reply with Quote
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
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
794 Posts

Posted - 05/07/2013 :  13:59:24  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000