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)
'Eric D. Burdo' on Fri, 03 Aug 2007 13:23:40 GMT, sez: I usually use the CASE method (it was used by a previous developer, so I "inherited" it.
I haven't bothered testing speed... works fast enough. :)
'Peter' on Fri, 03 Aug 2007 14:17:30 GMT, sez: How about something as basic as:
SELECT COUNT(*) FROM MyTableWhatHasABitColumn WHERE MyBitField=1
(or should it be TRUE instead of 1 ?)
'Sjoerd Verweij' on Fri, 03 Aug 2007 16:56:58 GMT, sez: This is news to you?
By the way, Peter: it should be 1. SQL Server has no true boolean.
'Jake' on Sat, 04 Aug 2007 13:08:40 GMT, sez: @Sjoerd:
regarding: "This is news to you"
-- the first line is 'This is an oldie but a goodie' -- so i expect you didn't read very clearly
amd readomg: "By the way, Peter: it should be 1. SQL Server has no true boolean."
You have both missed the point!
When you need to count the bit fields it is usually just one aggregate amongst a number of others -- and when you try to perform a seemingly straight forward aggregate on a bit column -- SQL stops you.
These are some handy, proven methods for getting around it -- not new methods, but useful when you need them.
I think that this kind of 'TIPS and TRICKS' article is provided more for posterity than as gossip to help for this week in the news.
Jake Kes.
'Gordon' on Wed, 12 Sep 2007 13:17:57 GMT, sez:
sum(case when col_X = 1 then 1 else 0 end)
from tbl_X
'Ron Moses' on Thu, 27 Dec 2007 09:54:13 GMT, sez: Very helpful, thanks. I went with:
CAST(MIN(CAST(MyBitField AS int)) AS bit)
ron
|
secretGeek .:dot Nuts about dot Net:.
Is the remote control a thing of the past?
The Best Software Writing I
The Business Of Software (Eric Sink)
bookmark at Del.icio.us!
reddit this