Pages

Tuesday, 13 December 2011

T-SQL Tuesday #25 - T-SQL Tricks - Selective aggregates


Firstly, thanks to Allen White for hosting T-SQL Tuesday #25.

This month's T-SQL Tuesday is about T-SQL Tips and Tricks.  I mulled over a couple of ideas I had and eventually settled on showing a way to do selective aggregates.  Not because I necessarily think its a trick, though it sort of is, but because I keep seeing this done in other terrible ways (as we'll see).

Selective Aggregates

So what is a selective aggregate?
Well I'm not even sure that's an official name.  I just made it up but it does describe the approach.  Its best described with an example, and I'm going to use our old friend AdventureWorks (get it from CodePlex)

This is a very trivial example, but imagine we wanted to get an aggregate (we're going to use COUNT) of sales by month and year. Well that's quite easy:

select
    year(OrderDate)
    ,month(OrderDate)
    ,count(*)
from
    Sales.SalesOrderHeader
group by
    year(OrderDate)
    ,month(OrderDate)
order by
    year(OrderDate)
    ,month(OrderDate)

Results


Boom! And there you have it.  But what if we wanted to have years across the top...just like your CFO asked so (s)he could compare year on year?