Skip to content
Advertisement

count and sum in case statement

What is the difference below if I use case instead of sum? I believe I would get the same output?

SELECT SUM(CASE WHEN salary > 100000 THEN 1 ELSE 0 END) AS Total

SELECT COUNT(CASE WHEN salary > 100000 THEN 1 END) AS Total

SELECT COUNT(CASE WHEN salary > 100000 THEN 1 ELSE NULL END) AS Total

Thanks!

Advertisement

Answer

The three are equivalent. All of them count the number of rows that meet the particular condition (salary > 100000). All return 0/1 and would not return NULL values for the column.

From a performance perspective, all should be equivalent as well. I have a personal preference for the first version. I consider the third to be unnecessarily verbose because else NULL is the default for a case expression.

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement