count of column by different values in same row

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Does the "sample1" value reference something in a lookup table? If so, you can query that, and then two inline-subqueries will get you the counts.

If not, a query like this will work:

select c1, sum(case when c2 = 0 then 1 else 0 end) as total_0s, sum(case when c2 != 0 then 1 else 0 end) as total_1s

from MyTable

group by c1;

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

I assume this is a bit field?

select COUNT(*) - sum(cast(c2 as Int)) as Zero,

sum(cast(c2 as Int)) as One

from table1

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

nevermind, I missed the grouping. Gus's is the correct way. I misread and thought you were just looking for a total count. :blush:

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

roy.tollison SSCommitted Points: 1930 More actions · Answer 4

Thats what i needed thanks again for the info/example. worked great.