Click here to Skip to main content
13,251,398 members (69,717 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


1 bookmarked
Posted 28 Mar 2012

Bitwise OR aggregate

, 28 Mar 2012
Rate this:
Please Sign up or sign in to vote.
Performing a bitwise OR aggregate in SQL Server

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 DISTINCT values: SUM ( DISTINCT sourcecolumn ). This, then, can be used as a bitwise OR aggregate function, provided the values in the column are powers of two.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer (Senior)
United States United States
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, acknowledged pedant and contrarian


"Using fewer technologies is better than using more." -- Rico Mariani

"Good code is its own best documentation. As you’re about to add a comment, ask yourself, ‘How can I improve the code so that this comment isn’t needed?’" -- Steve McConnell

"Every time you write a comment, you should grimace and feel the failure of your ability of expression." -- Unknown

"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

"Use vertical and horizontal whitespace generously. Generally, all binary operators except '.' and '->' should be separated from their operands by blanks."

"Omit needless local variables." -- Strunk... had he taught programming

You may also be interested in...

Comments and Discussions

QuestionThis is not right for all cases Pin
tsr851-Apr-12 1:00
membertsr851-Apr-12 1:00 
This is not going to work in case your bitwise value holds more than 1 value ( for example 5 hold 1|4 )..

You could write your own bitwise_sum aggrigation function using CLR.. For example:

    Format.UserDefined, //use clr serialization to serialize the intermediate result
    IsInvariantToNulls = true, //optimizer property
    IsInvariantToDuplicates = false, //optimizer property
    IsInvariantToOrder = false, //optimizer property
    MaxByteSize = 8000) //maximum size in bytes of persisted value
public class BitWise_Sum : IBinarySerialize
    /// <summary>
    /// The variable that holds the intermediate result of the concatenation
    /// </summary>
    private long intermediateResult;
    /// <summary>
    /// Initialize the internal data structures
    /// </summary>
    public void Init()
        this.intermediateResult = 0;
    /// <summary>
    /// Accumulate the next value, not if the value is null
    /// </summary>
    /// <param name="value"></param>
    public void Accumulate(SqlInt64 value)
        if (value.IsNull)
        this.intermediateResult |= value.Value;
    /// <summary>
    /// Merge the partially computed aggregate with this aggregate.
    /// </summary>
    /// <param name="other"></param>
    public void Merge(BitWise_Sum other)
        this.intermediateResult |= other.intermediateResult;
    /// <summary>
    /// Called at the end of aggregation, to return the results of the aggregation.
    /// </summary>
    /// <returns></returns>
    public SqlInt64 Terminate()
        return this.intermediateResult;
    public void Read(BinaryReader r)
        intermediateResult = Convert.ToInt64(r.ReadString());
    public void Write(BinaryWriter w)

Success comes to those who have the courage to try.

AnswerRe: This is not right for all cases Pin
PIEBALDconsult1-Apr-12 18:00
memberPIEBALDconsult1-Apr-12 18:00 
AnswerRe: This is not right for all cases Pin
Igor Voynovskyy4-Apr-15 1:43
memberIgor Voynovskyy4-Apr-15 1:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171114.1 | Last Updated 28 Mar 2012
Article Copyright 2012 by PIEBALDconsult
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid