5
SUM(CASE WHEN column1 = 'value1' THEN 1 ELSE 0 END),
SUM(CASE WHEN column2 = 'value2' THEN 1 ELSE 0 END)..

I don't know why I am finding it difficult to understand what exactly is happening in the above statement and what exactly is 'Then 1 else 0' doing here.

Ravi
  • 667
  • 3
  • 9
  • 19
  • 1
    Semi-related, but don't use SUM in this case (if possible), use COUNT http://dba.stackexchange.com/questions/119945/why-are-multiple-count-faster-than-one-sum-with-case – Erik Bergstedt Nov 18 '15 at 13:08

2 Answers2

14

If column1 contains the value value1 then the CASE expression will return 1, and SUM() will add 1 for that row. If it doesn't, the CASE expression will return 0, and it will add 0 for that row. This is a way to count how many rows have value1 in column1, but there are other ways to do this too, e.g. on 2012+:

SELECT COUNT(IIF(column1 = 'value1', 'truish', NULL))

On earlier versions:

SELECT COUNT(CASE WHEN column1 = 'value1' THEN 'truish' END)

(You don't need the ELSE here because, unlike SUM, COUNT ignores NULL.)

And I updated the value 1 to be a string to demonstrate that this has nothing to do with boolean.

Aaron Bertrand
  • 180,303
  • 28
  • 400
  • 614
  • Thank you, I must be incredibly dumb, couldn't even understand this. Now that you explained it the way you did, it makes sense. It is finally adding all the 1s for every occurrence of the 'value' in the example I have posted.

    I also wanted to ask, is it the same as :

    CASE WHEN column1 = 'Value1' THEN COUNT(column1) ELSE 0 END AS SomeAliasForCol1Count

    And then using:

    GROUP BY Column1

     – Ravi Nov 03 '15 at 21:15
  • @Ravi No idea, what is that query supposed to return? I don't understand why it would group by the same column you're using inside the aggregate. Why not just SELECT column1, COUNT(*) ... GROUP BY column1;? – Aaron Bertrand Nov 03 '15 at 22:29
  • Aaron, without that it shows me the error about column not being a part of a GROUP BY clause. That is, if I use Count function in the THEN part of a case statement. You're right about this, I could just use it separately, I was trying to make it appear similar to the multiple SUM functions in the question and see how the same thing would work with COUNT. – Ravi Nov 04 '15 at 14:22
3

That is equivalent to a COUNT IF. Some people use it to do poor man's unpivot.

SQL evaluates the case statement first, which will return either a 0 or 1. Then the SUM operation adds up the values.

You can make the CASE statement as complex as you need it to be, even looking up data in other tables.

Jonathan Fite
  • 8,666
  • 1
  • 23
  • 30