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)Next → ← Previous
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.