In a few of the tables I'm working with, I have some bit-mapped values. In other words, there is a numeric (integer) column and the values are all powers of two (1, 2, 4, 8, etc.) and each power of two has a different meaning. When I want to summarize this data, I want to perform a bitwise OR on the values for a particular entity, for example if a particular entity has records with values of 1, 2, and 8, then I want to show the value 11. SQL Server has a bitwise OR operator (
|), but it doesn't have a bitwise OR aggregate function. In the simple example above, you may notice that
1 | 2 | 8 == 1 + 2 + 8 so it would be good if we could use the SUM aggregate function
SUM ( sourcecolumn ), but that won't work if any of the values are duplicated:
1 | 1 | 2 | 8 != 1 + 1 + 2 + 8 . Fortunately, you can use the
SUM function to add up only the
SUM ( DISTINCT sourcecolumn ). This, then, can be used as a bitwise OR aggregate function, provided the values in the column are powers of two.
BSCS 1992 Wentworth Institute of Technology
Originally from the Boston (MA) area. Lived in SoCal for a while. Now in the Phoenix (AZ) area.
OpenVMS enthusiast, ISO 8601 evangelist, photographer, opinionated SOB
"If you need help knowing what to think, let me know and I'll tell you." -- Jeffrey Snover [MSFT]
"Typing is no substitute for thinking." -- R.W. Hamming
"I find it appalling that you can become a programmer with less training than it takes to become a plumber." -- Bjarne Stroustrup
ZagNut’s Law: Arrogance is inversely proportional to ability.
"Well blow me sideways with a plastic marionette. I've just learned something new - and if I could award you a 100 for that post I would. Way to go you keyboard lovegod you." -- Pete O'Hanlon
"linq'ish" sounds like "inept" in German -- Andreas Gieriet
"Things would be different if I ran the zoo." -- Dr. Seuss
"Wrong is evil, and it must be defeated." – Jeff Ello
"A good designer must rely on experience, on precise, logical thinking, and on pedantic exactness." -- Nigel Shaw
“It’s always easier to do it the hard way.” -- Blackhart
“If Unix wasn’t so bad that you can’t give it away, Bill Gates would never have succeeded in selling Windows.” -- Blackhart
"Omit needless local variables." -- Strunk... had he taught programming
"We learn more from our mistakes than we do from getting it right the first time."
My first rule of debugging: "If you get a different error message, you're making progress."
My golden rule of database management: "Do not unto others' databases as you would not have done unto yours."
My general rule of software development: "Design should be top-down, but implementation should be bottom-up."