Difference between sum and count in aggregate case statement

Question

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!

Answer 1

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