Thursday, December 16, 2004

SUM on bit columns (SQL)

When using SUM on a field ('bitcolumn') with the data type 'bit', you may not get the expected result - i.e. you would expect it to return the count of all the rows that have this field set to true (1).

SELECT SUM(bitcolumn)

However, it returns an error: The sum or average aggregate operation cannot take a bit data type as an argument.

You can however, use CAST to convert 'bit' to 'int', and perform SUM on the result.

SELECT SUM(CAST(bitcolumn As int))

This can however result in NULL returned as a value, when you want 0 instead. To always get a number as the result, you can also use COUNT combined with CAST and NULLIF. The key is that COUNT does not include any rows with NULL set on the field you are querying. CAST converts to 'int' and NULLIF converts the 0's to NULL's. COUNT therefor only includes the row count you want (where the field value is 1).

SELECT COUNT(CAST(NULLIF (bitcolumn, 0) AS int))

3 comments:

Anonymous said...

Hi Sam,

What happens if the field you want to sum is a yes/no field which instead of giving a 1/0 value, is returning a -1/0?

Im tearing my hair out!!!

amanda said...
This comment has been removed by a blog administrator.
Sam said...

I have not tried this yet, but I think it will work if you use CASE:

SELECT SUM(CASE bitcolumn WHEN -1 THEN 1 ELSE 0 END)