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