T-SQL 2005: Counting All Rows and Rows Meeting Criteria

Question

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:

SELECT
   COUNT(*)
   ,COUNT(CASE WHEN BooleanColumn = 1 THEN 1 ELSE 0 END)
FROM
   MyTable
GROUP BY
   KeyColumn

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?

Answer 1

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

Answer 2

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

COUNT counts the NON-NULL rows.

Answer 3

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