Always Get Better

How to SUM Bit Fields in SQL

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

SELECT SUM(blnBitColumn) FROM tblTable;

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

SELECT SUM(CONVERT(int,blnBitColumn)) FROM tblTable;

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:

SELECT COUNT(blnBitColumn)-SUM(CONVERT(int,blnBitColumn)) FROM tblTable;


One Response to “How to SUM Bit Fields in SQL”

  1. Ben Hudson says:

    A few ways to tackle this issue.

    Counting true bits
    – SELECT COUNT(NULLIF(blnBitColumn,0)) From tblTable

    Counting False bits
    – SELECT COUNT(NULLIF(blnBitColumn,1)) From tblTable

Leave a Reply