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

Tim Nelson <timnelson(at)phreaker(dot)net> schrieb:

> 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.
>
> Does anyone have a way around this? Thanks!
>
> select
> type,
> sum(sales),
> sum(cost),
> (sum(sales) * sum(cost) / sum(sales)) * 100

You need a case-statement like this example:

test=> select * from foo;
id | wert
----+------
0 | 0
1 | 1
2 |
(3 Zeilen)

test=> select id, case when wert != 0 then 5/wert end from foo;
id | case
----+--------------------
0 |
1 | 5.0000000000000000
2 |
(3 Zeilen)

HTH, Regards, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°