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.

1

2 Answers 2

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.

3
  • 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 3, 2015 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;? Nov 3, 2015 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 4, 2015 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.

5
  • So 1 or 0 is like a boolean values, like true or false in that case?
    – Ravi
    Nov 3, 2015 at 20:22
  • That's the general effect. The CASE Statement is the boolean operator and the THEN and ELSE is the return. Nov 3, 2015 at 20:23
  • 4
    @Ravi No, it's not really a boolean value. SUM is either going to count that row or not. Nov 3, 2015 at 20:24
  • 5
    (Also, just being pedantic about semantics, but in T-SQL, CASE is an expression, not a statement - more here.) Nov 3, 2015 at 20:30
  • I had to look up and read about differences, thank you for pointing this out. I conflate this with something like an IF statement.
    – Ravi
    Nov 3, 2015 at 21:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.