How to aggregate a bit column
secretGeek .:dot Nuts about dot Net:.
home .: about .: sign up .: sitemap .: secretGeek RSS

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




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 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.

Articles

SQL Style Extensions for C# SQL Style Extensions for C#
The Movie Hollywood (And My Wife) Doesn't Want You To See: Weekend at Jacko's The Movie Hollywood (And My Wife) Doesn't Want You To See: Weekend at Jacko's
Sysi: the ultimate administrators toolkit Sysi: the ultimate administrators toolkit
Movie: Priest Academy Movie: Priest Academy
Inspirational Rat Story Inspirational Rat Story
A face-melting DSL that allows programming ON the iPhone (and iPad) A face-melting DSL that allows programming ON the iPhone (and iPad)
The secretGeek Disaster Recovery plan The secretGeek Disaster Recovery plan
Save KNVTn! Before it's too late Save KNVTn! Before it's too late
The Ultimate Agent of WERF Destruction The Ultimate Agent of WERF Destruction
The new prisoner's dilemma The new prisoner's dilemma
Original Premise for a road movie Original Premise for a road movie
What's a better game than Devshop? What's a better game than Devshop?
DevShop: The Cool Game that Makes Development Look Fun DevShop: The Cool Game that Makes Development Look Fun
Should be purple Should be purple
Kitchen Agile Kitchen Agile
Perhaps Perhaps "Go" is the new Visual Basic
zen-coding: turn those CSS selectors upside down zen-coding: turn those CSS selectors upside down
Debugging: It's all about finding Albuquerque. Debugging: It's all about finding Albuquerque.
The Real-Time online JQuery Editor The Real-Time online JQuery Editor
HTML5, a 3 minute guide HTML5, a 3 minute guide
Developer Codpieces Developer Codpieces
Agile for one: The Personal Story 'Wall' In Action Agile for one: The Personal Story 'Wall' In Action
Never work with thick people. Never work with thick people.
Cosmo: project status panel Cosmo: project status panel
Windows Search in Japan Windows Search in Japan
Project Management Zen Project Management Zen
Continuous Integration, Plugins and Going Too Far Continuous Integration, Plugins and Going Too Far
The Rules of Stand Up The Rules of Stand Up
Sydney International Airport: Stupid, Criminal, or Criminally Stupid? Sydney International Airport: Stupid, Criminal, or Criminally Stupid?
God No! ...The ReBuilder God No! ...The ReBuilder
Matt, The Office Mortar Matt, The Office Mortar
'Outlook style' rules for Subversion 'Outlook style' rules for Subversion
Really deep linking: Url + regex Really deep linking: Url + regex
hExcel -- A Hexagonal Spreadsheet hExcel -- A Hexagonal Spreadsheet
Is the remote control a thing of the past? Is the remote control a thing of the past?
The Utterly Thorough Guide To Awesome Application Compatibility on Windows 7. The Utterly Thorough Guide To Awesome Application Compatibility on Windows 7.
Astounding Hyperlinked Noticeboard Astounding Hyperlinked Noticeboard
Three Questions About Each Bug You Find Three Questions About Each Bug You Find
Recursing over the Pareto Principle... Recursing over the Pareto Principle...
Sometimes, The Better You Program, The Worse You Communicate. Sometimes, The Better You Program, The Worse You Communicate.

Archives .: secretGeek :: Complete Archives
TimeSnapper -- Automated Screenshot Journal TimeSnapper.com    
Version 3.3: true productivity boost

Next Action NextAction
Managing the top of your mind

World's Simplest Code Generator (html edition) World's Simplest Code Generator

25 steps for building a Micro-ISV 25 steps for building a Micro-ISV
3 minute guides -- babysteps in new technologies: powershell, JSON, watir, F# 3 Minute Guide Series
Universal Troubleshooting checklist Universal Troubleshooting Checklist
Top 10 SecretGeek articles Top 10 SecretGeek articles
ShinyPower (help with Powershell) ShinyPower
Now at CodePlex

Realtime CSS Editor, in a browser RealTime Online CSS Editor
Gradient Maker -- a tool for making background images that blend from one colour to another. Forget photoshop, this is the bomb. Gradient Maker


[powered by Google] 


How to be depressed How to be depressed
You are not inadequate.



Recommended Reading

The Best Software Writing I
The Business Of Software (Eric Sink)

Recommended blogs

Jeff Atwood
Reginald Braithwaite
Joseph Cooney
Phil Haack
Scott Hanselman
Julia Lerman
Rhys Parry
Joel Pobar
OJ Reeves
Eric Sink
Joel Spolsky
Des Traynor

Aggregated Links

programming.reddit.com
dzone
dot net kicks

Human Link Machines

interesting finds
a continuous learner's weblog
arjan's world
n links today
new and notable
morning coffee
learning .net
weekly link post
(my del.icio.us account)

LinkedIn profile
 
home .: about .: sign up .: sitemap .: secretGeek RSS .: © Leon Bambrick 2006 .: privacy

home .: about .: sign up .: sitemap .: RSS .: © Leon Bambrick 2006 .: privacy