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:

SELECT
        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',
FROM
        MyTableWhatHasABitColumn


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)

My book "Choose Your First Product" is available now.

It gives you 4 easy steps to find and validate a humble product idea.

Learn more.