How to aggregate a bit column

'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

name


website (optional)


enter the word:
 

comment (HTML not allowed)

All viewpoints welcome. But the right to delete any post for any reason is reserved. Don't make me do it. Comments may be republished, emailed to your loved ones or printed and used as toilet paper. Who reads this legal bit anyhow?

TimeSnapper_{Know thyself!}

TimeSnapper is a life analysis system that stores and plays-back your computer use. It makes timesheet recording a breeze, helps you recover lost work and shows you how to sharpen your act.

TimeSnapper won last year's Developer Competition at Larkware.com, and is used by over 10,000 people.