Обсуждение: 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
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
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
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
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°
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
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/
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.
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