Why does this simple query result in a "division by zero" Error?

select 
case when b > 0 then sum(a / b) end
from (values (1,1),(2,1),(1,0),(2,0)) t (a,b)
group by b

I would expect the output:

The only explanation I have is that postgres calculates the sum before doing the grouping and evaluating the case.

CodePudding user response：

See the documentation:

[…] a CASE cannot prevent evaluation of an aggregate expression contained within it, because aggregate expressions are computed before other expressions in a SELECT list or HAVING clause are considered. For example, the following query can cause a division-by-zero error despite seemingly having protected against it:

SELECT CASE WHEN min(employees) > 0
            THEN avg(expenses / employees)
       END
FROM departments;

So it is expected that aggregate expression sum(a/b) are computed before other expressions. This applies not only to Postgresql, but also to Sql Server too.

CodePudding user response：

Use NULLIF() to avoid the problem, you don't need a CASE for this:

SELECT  
     SUM(a / NULLIF(b,0))
FROM 
    (values (1,1),(2,1),(1,0),(2,0)) t (a,b)
GROUP BY b
ORDER BY 1; -- first (and only) column