Re: getting around---division by zero on numeric

Tim Nelson <timnelson(at)phreaker(dot)net> writes:
> I am getting division by zero on a calculated field ( sum(sales) is 0 )
> and I can't find a way around this. I figured out you can't use an
> aggregate in a where, and using having the parser must (obviously)
> evaluate the select fields before considering teh having clause.

Nothing "obvious" about that, in fact the spec says the opposite.

> select
> type,
> sum(sales),
> sum(cost),
> (sum(sales) * sum(cost) / sum(sales)) * 100
> from test
> group by 1
> having sum(sales) != 0

This should indeed work. If it doesn't, it means you are using an old
version of Postgres. It was fixed in 7.4.4:

2004-07-10 14:39 tgl

* src/backend/executor/: nodeAgg.c (REL7_4_STABLE), nodeAgg.c: Test
HAVING condition before computing targetlist of an Aggregate node.
This is required by SQL spec to avoid failures in cases like
SELECT sum(win)/sum(lose) FROM ... GROUP BY ... HAVING sum(lose) >
0; AFAICT we have gotten this wrong since day one. Kudos to Holger
Jakobs for being the first to notice.

As other respondents noted, you can work around the problem in various
ways ... but you shouldn't have to.

regards, tom lane