x

SUM(CASE ...) vs. CASE WHEN [field] = 'x' THEN SUM()

Should the syntax SUM(CASE ...) always be used or are there times when the CASE WHEN = 'x' then SUM() form be used when creating a conditional total? (This question arises from Phil Factor's answer in one of today's quesitons here).

more ▼

asked Dec 02 '10 at 08:15 AM in Default

Mark gravatar image

Mark
2.6k 21 25 27

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

OK. You all have adventureworks. In order to get a report of the number of create and Alter Table statements, others, and the total, here's what I'd do, using this technique. It is probably not the most elegant but it allows you to tweak things to taste!

USE Adventureworks
SELECT
  SUM(CASE WHEN event = 'Create_Table' 
           THEN 1 ELSE 0 END) AS CreateTable,
  SUM(CASE WHEN event = 'Alter_Table' 
           THEN 1 ELSE 0 END) AS AlterTable,
  SUM(CASE WHEN event NOT IN ('Create_Table', 'Alter_table') 
           THEN 1 ELSE 0 END) AS [Other],
  COUNT(*) AS total
FROM    dbo.DatabaseLog

--to maybe do this, for example...

SELECT
  COALESCE([schema],'Sum'),
  SUM(CASE WHEN event = 'Create_Table' 
           THEN 1 ELSE 0 END) AS CreateTable,
  SUM(CASE WHEN event = 'Alter_Table' 
           THEN 1 ELSE 0 END) AS AlterTable,
  SUM(CASE WHEN event NOT IN ('Create_Table', 'Alter_table') 
           THEN 1 ELSE 0 END) AS [Other],
  COUNT(*) AS total
FROM    dbo.DatabaseLog
GROUP BY [schema] WITH rollup
ORDER BY GROUPING([Schema]) 
more ▼

answered Dec 02 '10 at 12:36 PM

Phil Factor gravatar image

Phil Factor
3.8k 8 9 16

excellent +1 - now I don't need to find the throw-away code that i threw away :o)
Dec 02 '10 at 12:41 PM WilliamD
dang this case-sensitive copy of AdventureWorks 8)
Dec 02 '10 at 06:23 PM KenJ
(comments are locked)
10|1200 characters needed characters left

It can lead to inconsistent coding like

CASE WHEN [field] = 'x' THEN SUM() ELSE CONSTANT ...

I always stick with

SUM(CASE...)
more ▼

answered Dec 02 '10 at 09:05 AM

ozamora gravatar image

ozamora
1.4k 2 3 5

(comments are locked)
10|1200 characters needed characters left

If you compate the two in an execution plan they are pretty much the same. I tried it with a table variable and throw-away code and saw that one did a sort then aggregate, the other an aggregate then a sort. The costs of them were the same, as they were doing a table scan.

I can't imagine that it has a performance difference either way (YMMV - TEST TEST TEST!), but as ozamora states, it is easier to read and understand a SUM(CASE...), but that is personal preference.
more ▼

answered Dec 02 '10 at 11:39 AM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977
x39

asked: Dec 02 '10 at 08:15 AM

Seen: 10246 times

Last Updated: Dec 02 '10 at 08:27 AM