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

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@aqsacom.com
tél : 01 69 29 36 18
----------------------------------------------------------------------------
---------------




-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tim Nelson
Sent: mercredi 19 octobre 2005 14:27
To: pgsql-general@postgresql.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