Despite the cool things you can do with writing your own custom aggregate functions as shown in my last article, in many cases you don't need custom functions to get what you need.  This should answer some of the questions raised in the comments from my article.  Note that a couple of solutions shown require a Tally table, or a permanent table of numbers in your database with values from 0 to around 1,000.  In my examples, the tally table is called “numbers” and the integer column of numbers is simply “n”.

Aggregate Boolean OR ( 1= true, 0 = false):

SELECT GroupVal, MAX(CASE WHEN boolean expr THEN 1 ELSE 0 END)

FROM tbl

GROUP BY GroupVal

Aggregate Boolean AND:

SELECT GroupVal, MIN(CASE WHEN boolean expr THEN 1 ELSE 0 END)

FROM tbl

GROUP BY GroupVal

Aggregate Binary OR:

Method 1:

SELECT GroupVal, MAX(value & 1) + MAX(value & 2) + MAX(value & 4) + MAX(value & 8) + .... (as many bits as you need, incrementing in powers of 2)

FROM tbl

GROUP BY GroupVal

Method 2:

SELECT GroupVal, SUM(Distinct Value & 1) + SUM(Distinct Value & 2) + SUM(Distinct Value & 4) + SUM(Distinct Value & 8) ....

FROM tbl

GROUP BY GroupVal

Method 3 (using a Tally table):

SELECT GroupVal, SUM(Distinct Bits)

FROM

  (

    SELECT GroupVal, N, Value & power(2,N) as bits

    FROM tbl

    CROSS JOIN Numbers

    WHERE N between 0 and 15  -- or how many bits you need

  ) A

GROUP BY GroupVal

Aggregate Binary AND:

Method 1:

SELECT GroupVal, MIN(value & 1) + MIN(value & 2) + MIN(value & 4) + MIN(value & 8) +  ....

FROM tbl

GROUP BY Group Val

Method 2 (using a Tally table):