How to aggregate a bit column

This is an oldie but a goodie:

When you first try to count the number of true values in a bit column (in SQL Server) you get this error:

"The sum or average aggregate operation cannot take a bit data type as an argument."

(caused by a query such as...

"SELECT SUM(MyBitField) FROM MyTableWhatHasABitColumn")

With droyad's help we came up with four ways to get around the problem:

        SUM(CAST(MyBitField AS INT)) AS '1st Technique',
        SUM(CASE(MyBitField) WHEN 1 THEN 1 ELSE 0 END) AS '2nd Technique',
        COUNT(NULLIF(MyBitField,0)) AS '3rd Technique',

4th Technique: Another approach is to get a bigger hammer. In SQL Server 2005 you can create user defined aggregates, in the .net language of your choice. But that was the specific path I was trying to avoid going down.

Comparing the performance of these four techniques is left as an exercise for the avid reader. My favourite is the "COUNT NULLIF." I hope it ain't too slow.

(sorry for the straight nerd talk. not feel like telling funny stories this week)


I'm currently writing a book about how to build your first product. If you want to build your first product, please sign up to be notified when the book is available.

(By the way, I read every comment and often respond.)

Your comment, please?

Your Name
Your Url (optional)
Note: I may edit, reuse or delete your comment. Don't be mean.