What does Then 1 else 0 mean in a CASE expression used with an aggregate function?

Question

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.

Answer 1

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.

Answer 2

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.