# Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 155, comments - 2679, trackbacks - 64

### News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.

Subscribe

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

If you have any questions about these techniques or would like more information, just ask !

Print | posted on Sunday, July 11, 2004 10:12 PM | Filed Under [ T-SQL Code Library - SQL ]

## #re: More aggregate T-SQL functions

> 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

You can also express this as:

SELECT GROUPVAL
, MAX (value & POWER (2, 0))
+ MAX (value & POWER (2, 1))
+ MAX (value & POWER (2, 2))
+ MAX (value & POWER (2, 3))
+ ...
+ MAX (value & POWER (2, n - 1)

This expression can be useful if you're building a piece of dynamic T-SQL through iteration in a loop. The second argument of POWER is then based on the loop counter, and you don't need to keep all the values of those powers of 2 in your head. Personally, I get lost somewhere around 4096; it's easier to remember POWER (2, 12).

7/27/2004 6:52 PM | Lee Dise
Comments have been closed on this topic.