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

Tim Nelson <timnelson@phreaker.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°