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

It can lead to inconsistent coding like

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

I always stick with

SUM(CASE...)

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.

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