Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at CFUNITED 2008 (Washington, D.C.) with:

SQL Aggregates Support CASE Statements

By Ben Nadel on

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 :)

Tweet This Provocative thoughts by @BenNadel - SQL Aggregates Support CASE Statements Thanks my man — you rock the party that rocks the body!



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 :)

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 ;) ).

This is My Table

id Type_id Points
1 19 500
1 20 200

select * from points where id=1
when i run this query the return like same as above
but i want to show like this

id Type_id BPoints Type_id GPoints
1 19 500 20 200

can any one me for this

I need help for below mention query, can some one help me :), really stuck with this query.

SELECT CASE appresptime
WHEN appresptime >= 0 AND appresptime <= 5 THEN
'0-5 Sec'
-- WHEN (t.appresptime > 5) THEN
--'5 Above'
ELSE
'normal'
END CASE,
round(t.appresptime / 1000, 0),
count(t.id)
from abc_hist t
where t.requesttime between trunc(sysdate - 1) and trunc(sysdate)
group by t.appresptime

Getting Error for Missing Right Parenthesis

Thanks.