Coalesce not evaluating second argument?

Question

I am trying to run the following query:

SELECT COALESCE(count(percent_cov), 0)
FROM sample_cov
WHERE target = 542
GROUP BY percent_cov
HAVING percent_cov < 10

Basically, I want to show the number of times this statistic was < 10, and return 0 rather than null if the count was 0. If the count is >0 I get the number I want as the result, however if the count is 0 I still get a null returned. (Same thing if I set the second argument to coalesce as a positive number). What am I doing wrong?

Answer 1

I rewrote your query the way I think you want it:

SELECT count(*) AS ct
FROM   sample_cov
WHERE  target = 542
AND    percent_cov < 10;

count() returns 0 When no matching rows (or non-null values in the column) are found. No need for coalesce(). I quote the manual on this:

It should be noted that except for count, these functions return a null value when no rows are selected.

Bold emphasis mine. If you want to return a different value when count() comes back with 0, use a CASE statement.

Also, it's no use to write count(percent_cov) while you have WHERE percent_cov < 10. Only non-null values qualify, count(*) yields the same result slightly faster and simpler in this case.

You don't need a GROUP BY clause as you don't group by anything, you are aggregating over the whole table.
You could GROUP BY target, but this would be a different query:

SELECT target, count(*)
FROM   sample_cov
WHERE  percent_cov < 10
GROUP  BY target;

You would need to spell out the expression in the HAVING clause again. Output column names are visible in ORDER BY and GROUP BY clauses, not in WHERE or HAVING.