# Jeff Smith Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server

## More aggregate SQL functions

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):

SELECT GroupVal, SUM(bits)
FROM
(

SELECT GroupVal, MIN(Bits) as bits, N
FROM
(
SELECT GroupVal, N, Value & POWER(2,N) as bits
FROM tbl
CROSS JOIN Numbers
WHERE Numbers.N between 0 and 15  -- or how many bits you need
) A
GROUP BY GroupVal, N
) A

GROUP BY GroupVal