Difference between sum and count in aggregate case statement

user_8797830 0
Viewed 620

I have a query below as:

    SELECT 
       d.name, 
      SUM(CASE WHEN e.salary > 100000 THEN 1 ELSE 0 END)
        / COUNT(DISTINCT e.id)
        AS pct_above_100k,
      COUNT(DISTINCT e.id) AS c
FROM employees e JOIN departments d ON e.department_id = d.id
GROUP BY 1
HAVING COUNT(*) > 10
ORDER BY 2 DESC

I am using sum here, but would there be any difference if I was using count?

I know count counts the number of times a value exists and sum adds up the actual values, but here since my condition is if the salary is > 100000 it would look at that as 1 anyway right?

Thanks!

presto postgresql sql
3 Answers

We count conditionally with a filter clause:

COUNT(*) FILTER (WHERE e.salary > 100000)

Some other dBMS don't support the filter clause. Here we use workarounds by evaluating the expression ourselves with CASE WHEN and either use COUNT or SUM to add the matches up. Here are some ways to do this:

SUM(CASE WHEN e.salary > 100000 THEN 1 ELSE 0 END)
COUNT(CASE WHEN e.salary > 100000 THEN 1 ELSE NULL END)
COUNT(CASE WHEN e.salary > 100000 THEN 1 END)
COUNT(CASE WHEN e.salary > 100000 THEN 'count this' END)

As PostgreSQL does support the filter clause you should use COUNT(*) FILTER (...).

user_22707620

If you just replaced sum with count, you would get a different result, since 0 counts as much as 1.

The most elegant way to write this is a FILTER clause, as others have mentioned, but you could also do this:

count(CASE WHEN e.salary > 100000 THEN 0 END)

This would work because NULL values are ignored by (most) aggregate functions.

user_64643080

Presumably, employees are not in multiple departments, so the count(distinct) is unnecessary.

That means that the code can be more simply written as:

  AVG(CASE WHEN e.salary > 100000 THEN 1 ELSE 0 END) AS pct_above_100k,

Or:

  AVG( (e.salary > 100000)::int ) AS pct_above_100k,

These will be more efficient than the alternatives because COUNT(DISTINCT) is usually a bit more expensive than other aggregation functions.

user_11440350
Related