You could use a CASE statement...
select
type,
sum(sales),
sum(cost),
CASE WHEN sum(sales) <> 0 THEN (sum(sales) * sum(cost) / sum(sales)) *
100 ELSE 0 END
from test
group by 1
However, I guess that your example is just not what you really use as
sum(sales) * sum(cost) / sum(sales) seems very similar to sum(cost).....
----------------------------------------------------------------------------
---------------
Patrick Fiche
email : patrick.fiche@xxxxxxxxxxx
tél : 01 69 29 36 18
----------------------------------------------------------------------------
---------------
-----Original Message-----
From: pgsql-general-owner@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx]On Behalf Of Tim Nelson
Sent: mercredi 19 octobre 2005 14:27
To: pgsql-general@xxxxxxxxxxxxxx
Subject: getting around---division by zero on numeric
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
from test
group by 1
having sum(sales) != 0
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
--
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.12.4/142 - Release Date: 18/10/2005
--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.12.4/142 - Release Date: 18/10/2005
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
