From: | Patrick FICHE <Patrick(dot)FICHE(at)AQSACOM(dot)COM> |
---|---|
To: | Tim Nelson <timnelson(at)phreaker(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: getting around---division by zero on numeric |
Date: | 2005-10-19 12:56:51 |
Message-ID: | 1DC6C8C88D09D51181A40002A5286929B23AB2@intranet |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)fiche(at)aqsacom(dot)com
tél : 01 69 29 36 18
----------------------------------------------------------------------------
---------------
-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Tim Nelson
Sent: mercredi 19 octobre 2005 14:27
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] 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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2005-10-19 12:58:46 | Re: Create GLOBAL TABLE |
Previous Message | Marcel Gsteiger | 2005-10-19 12:36:46 | function that resolves IP addresses |