Easy data buckets with conditional sums

Recently I was populating some leverage summary tables. Leverage is a common statistic when working with law firms, although the exact calculation varies. The buckets are simply sums of timekeepers that are in title groups.

It would be a simple matter to group by title/title group and any other axis that is appropriate for the aggregate level. Unfortunately this would make the leverage buckets into different rows which wasn't useful for me. It would be possible to pivot the table, but SQL isn't well suited for this task.

That where a conditional sum comes into play:

SUM(CASE title WHEN 'PARTNER' THEN 0 ELSE value END)

In reality, I wrote a SQL function to convert titles into title groups but this was to simplify the SQL and reduce maintenance if the title groups ever changed.

So how does this work? The answer lies in how SQL executes queries. Aggregate functions are calculated late in the process, after grouping occurs. The case statement executes for each row that is returned. Values that don't belong in that bucket are simply transformed to 0.