SQL Aggregates Support CASE Statements

Posted March 15, 2007 at 12:05 PM by Ben Nadel

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

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.

Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment