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, 2010 at 08:15 AM in Default

avatar image

Mark
2.6k 24 27 31

(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, 2010 at 12:36 PM

avatar image

Phil Factor
4.2k 8 22 20

excellent +1 - now I don't need to find the throw-away code that i threw away :o)

Dec 02, 2010 at 12:41 PM WilliamD

dang this case-sensitive copy of AdventureWorks 8)

Dec 02, 2010 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, 2010 at 09:05 AM

avatar image

ozamora
1.4k 3 16 7

(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, 2010 at 11:39 AM

avatar image

WilliamD
26.2k 18 33 48

(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.

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:

x1066
x12

asked: Dec 02, 2010 at 08:15 AM

Seen: 17911 times

Last Updated: Dec 02, 2010 at 08:27 AM

Copyright 2016 Redgate Software. Privacy Policy