SQL Aggregates Support CASE Statements
Posted March 15, 2007 at 12:05 PM by Ben Nadel
Daniel Roberts, Tony Petruzzi, and Rick Osborne just taught me some revolutionary stuff in SQL. Unbeknownst to me, SQL aggregates (ex. SUM, COUNT) can handle CASE statements as part of their execution logic. This is HUGE! I can't believe I didn't know this (or rather, I can easily believe it, but I am saddened).
So just quickly, as this post is more about excitement than it is about teaching, let me just show you how I might rework my previous SQL with new CASE statements:
- SELECT
- g.hair,
- (
- COUNT( * )
- ) AS girl_count,
- (
- COUNT(
- CASE
- WHEN did_date = 1
- THEN did_date
- ELSE NULL
- END
- )
- ) AS did_date_count,
- (
- COUNT(
- CASE
- WHEN did_date = 0
- THEN did_date
- ELSE NULL
- END
- )
- ) AS did_not_date_count
- FROM
- @girl g
- GROUP BY
- g.hair
Thanks guys, for taking the time to teach some stuff :)
Reader Comments
Just want to point out that per record functions can be quite taxing for large queries versus other solutions.
I worked on a reporting site that originally ran from a log table in an Access database which was later moved to MSSQL. There was summing on t/f fields to come up with some counts and since Access t/f values are -1 the absolute value was found for summing. It looked like this: Sum(Abs(field)). Notice anything? Well the functions are improperly nested for this need. Just switching around the nesting to Abs(Sum(field)) greatly improvement execution time. That is 1 Abs() call versus N records of calls.
Queries on a well indexed database may perform better using subqueries to get counts and such versus aggregates on case statements. Just one more thing to think about :-)
You can actually make it simpler. because you are just counting, you can use a 1 instead of the field for the value to count, and if you want a null, you can just leave out the else:
# COUNT(
# CASE
# WHEN did_date = 0
# THEN 1
# END
# )
@Dan,
That is a good point. I think the kind of situations where I would use stuff like this are general less than optimal, so every little tip can help.
@Ryan,
This is true. Daniel eluded to something very similar in my earlier blog post (that prompted this one). Sometimes it's just a matter of standing back, taking stock of the situation, and realizing that there is more than one way to attack a problem.
... my part is the "realization" step :)
*my "problem" is with the realization step :)
As an aside, there's a second form of CASE that you don't see much, but you and your curious nature might want to play around with:
CASE
WHEN foo = 'A' THEN 1
WHEN foo = 'B' THEN 2
END
- versus -
CASE foo
WHEN 'A' THEN 1
WHEN 'B' THEN 2
END
I know that, on some systems, the latter case will speed up the query a hair. I think SQL Server is smart enough that it doesn't matter, but I remember rewriting a bunch of queries on the old version of DB2 we had here.
And also keep in mind that if you don't have an ELSE part, the CASE returns NULL. This may seem like a "duh", but think about how that affects aggregate functions:
SELECT
AVG(CASE WHEN a > 0 THEN a ELSE 0 END) AS WithZeros,
AVG(CASE WHEN a > 0 THEN a END) AS WithNulls
FROM (
SELECT 4 AS a UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT -1 UNION ALL
SELECT -2 UNION ALL
SELECT -3
) AS b
This returns: WithZeros = 2, WithNulls = 5
The AVG function doesn't treat NULLS as values - it basically pretends they aren't in the set. Thus, the WithZeros AVG uses a set of (4,5,6,0,0,0) for an average of 2, while the WithNulls AVG uses a set of (4,5,6,NULL,NULL,NULL), which collapses to (4,5,6) and thus averages out to 5.
Mean what you say and say what you mean.
@Rick,
Very cool. And this is awesome:
FROM (
SELECT 4 AS a UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT -1 UNION ALL
SELECT -2 UNION ALL
SELECT -3
) AS b
I have never seen anything like that before. That could be pretty useful at times. All good stuff!
Heh. Use with care. Not many DBMSes like that syntax and it is normally not optimized at all. SQL Server, obviously, is okay with it. DB2 barfs. mySQL v5 is okay with it. Access complains about the lack of tables.
I'll generally use the pivot table trick we've discussed previously, but in a pinch when you're not set up for one, a UNIONed subselect will do if the DBMS is okay with it.
Oh trust me, I make good use of the pivot table. I use it for pretty much anything that involves a number of months (a lot of the reporting I do). It has made my life much easier. You are a rock star (even if you have trouble with daylight savings ;) ).
