Take the 2-minute tour ×

Here's the scenario:

I have a table with 3 columns: 'KeyColumn', 'SubKeyColumn' and 'BooleanColumn', where the first two are the primary keys of the table.

For my query, I'd like to count the number of rows there are for any given value in 'KeyColumn', and I'd also like to know which ones have a value of true for 'BooleanColumn'. My initial thought was to create a query like this:

   ,COUNT(CASE WHEN BooleanColumn = 1 THEN 1 ELSE 0 END)

However, the 2nd part does not work (I'm not entirely sure why I thought it would to begin with). Is it possible to do something like this in one query? Or am I going to need to do multiple queries to make this happen?

share|improve this question
are you going to add KeyColumn to the query so you know what you're looking at? –  DForck42 Jun 25 '10 at 19:33
add comment

3 Answers

up vote 3 down vote accepted

Change COUNT to SUM in the 2nd part. ;)

share|improve this answer
Nice. Well that was easy... today is not my day. Thanks! –  John Jun 25 '10 at 19:23
pretty much what i was going to say. –  DForck42 Jun 25 '10 at 19:32
add comment

... CASE WHEN BooleanColumn = 1 THEN 1 ELSE NULL END ...

COUNT counts the NON-NULL rows.

share|improve this answer
I see; I guess it helps when you know what count() is actually counting! –  John Jun 25 '10 at 19:26
add comment

You could also do SUM(CAST(BooleanColumn AS TINYINT))

share|improve this answer
add comment

Your Answer


