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.
| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2003-09-30 : 03:53:57
|
| I'm not even clear that this is possible - but hey, I'll ask anyway. I want to produce two counts, one with a condition on itSo for example, my people table monitors ethnicity so SELECT ethnicity, count(*) from PEOPLE group by ethnicitywill give me one of the columns I want. e.g.ethnicity countUnknown 200Not stated 5000However I want to be able to compare this with the people with a certain condition, so SELECT ethnicity, count(*) from PEOPLE where id in select id from OtherTable group by ethnicitywill give me this e.g.ethnicity countUnknown 150Not stated 200What I want is to combine them into a single query so that It is easier to show the comparison and I can then analyse them e.g. show the differences etcso I would get ethnicity count count(with condition)Unknown 200 150Not stated 5000 200It sounds very simple but I can't get it to work!thankssteveSteve no function beer well without |
|
|
Stoad
Freaky Yak Linguist
1983 Posts |
Posted - 2003-09-30 : 04:06:01
|
| SELECTethnicity,count(*),sum(case when yourCondition then 1 else 0 end)from PEOPLE where id ....group by ethnicity |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2003-09-30 : 08:23:41
|
| D'OH!!Thanks very muchsteveSteve no function beer well without |
|
|
|
|
|
|
counting confusion