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

getting around---division by zero on numeric

From
Tim Nelson
Date:
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


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

From
Patrick FICHE
Date:
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


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

From
Sean Davis
Date:
On 10/19/05 8:26 AM, "Tim Nelson" <timnelson@phreaker.net> wrote:

> 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

Can you use case?

http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html

Or you could create a simple function to do the logic to avoid the division
by zero.

Sean


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

From
Richard Huxton
Date:
Tim Nelson wrote:
> I am getting division by zero on a calculated field ( sum(sales) is 0 )

It's a two-stage process, so you'll want a sub-query. Something like:

SELECT
   type,
   tot_sales,
   tot_cost
   ((tot_sales * tot_cost / tot_sales) * 100) AS percent
FROM
(
SELECT
   type, sum(sales) AS tot_sales, sum(cost) AS tot_cost
FROM
   test
GROUP BY
   type
HAVING
   sum(sales) <> 0
) AS base
;

--
   Richard Huxton
   Archonet Ltd

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

From
Andreas Kretschmer
Date:
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°

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

From
Michael Glaesemann
Date:
On Oct 19, 2005, at 21:26 , Tim Nelson wrote:

> 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

You might try a CASE expression like so:


select type
     , sum(sales) as sales_total
     , sum(cost) as cost_total
     , case when sum(sales) <> 0
     then (sum(sales) * sum(cost)/sum(sales)) * 100
     else 0
     end as calculation
from test
group by type;

I don't know what you want as a result when sum(sales) = 0; I just
put 0 in because you'll need a numeric result, (unless you cast to
text, for example, if you wanted to use '--' or '').

As an aside, but it's generally considered good practice to refer to
attributes by name rather than position. Also, while SQL does not
require it, a relation should have unique attribute names, which is
why I've aliased the attributes of the result. Otherwise you'll have
two attributes named "sum" (and another named "case", iirc, which
isn't really very descriptive).

Hope this helps.


Michael Glaesemann
grzm myrealbox com




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

From
Jerry Sievers
Date:
Tim Nelson <timnelson@phreaker.net> writes:

> 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

Suggest using a nested query approach;

select
    a,
    b/c as result
from (
    select
        a,
        sum(b) as b,
        sum(c) as c
    from foo
    group by a
    having (sum(c) != 0
    )
    as inner
;

Prevents the division operation from seeing a 0 and avoids the problem

HTH

--
-------------------------------------------------------------------------------
Jerry Sievers   305 854-3001 (home)     WWW ECommerce Consultant
                305 321-1144 (mobile    http://www.JerrySievers.com/

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

From
Berend Tober
Date:
Richard Huxton wrote:

> Tim Nelson wrote:
>
>> I am getting division by zero on a calculated field ( sum(sales) is 0 )
>
>
> It's a two-stage process, so you'll want a sub-query. Something like: ...

Thanks. That's a cool addition to my bag of tricks.

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

From
Tom Lane
Date:
Tim Nelson <timnelson@phreaker.net> writes:
> 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.

Nothing "obvious" about that, in fact the spec says the opposite.

> select
>     type,
>     sum(sales),
>     sum(cost),
>     (sum(sales) * sum(cost) / sum(sales)) * 100
> from test
> group by 1
> having sum(sales) != 0

This should indeed work.  If it doesn't, it means you are using an old
version of Postgres.  It was fixed in 7.4.4:

2004-07-10 14:39  tgl

    * src/backend/executor/: nodeAgg.c (REL7_4_STABLE), nodeAgg.c: Test
    HAVING condition before computing targetlist of an Aggregate node.
    This is required by SQL spec to avoid failures in cases like
    SELECT sum(win)/sum(lose) FROM ... GROUP BY ... HAVING sum(lose) >
    0; AFAICT we have gotten this wrong since day one.  Kudos to Holger
    Jakobs for being the first to notice.

As other respondents noted, you can work around the problem in various
ways ... but you shouldn't have to.

            regards, tom lane