Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

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





Archives

Post Categories

Programming

Sports

SQL

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 ]

Feedback

Gravatar

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

Powered by:
Powered By Subtext Powered By ASP.NET