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

Tim Nelson  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°