Always Get Better

By default, SQL Server doesn’t allow an operation like this:

[source:sql]
SELECT SUM(blnBitColumn) FROM tblTable;
[/source]

In order to achieve this result, you must first convert the bit column to a numeric type:

[source:sql]
SELECT SUM(CONVERT(int,blnBitColumn)) FROM tblTable;
[/source]

This counts the number of times the bit is true.

If you want to get the flip-side of that to see how many times the bit is false, just subtract the total number of bits from the positive:

[source:sql]
SELECT COUNT(blnBitColumn)-SUM(CONVERT(int,blnBitColumn)) FROM tblTable;
[/source]

Tags: SQL

This entry was posted on Sunday, April 6th, 2008 at 7:49 am and is filed under SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.