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

От:

Patrick FICHE <Patrick.FICHE@AQSACOM.COM>

Дата:

19 октября 2005 г. в 10:05:09

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

От:

Andreas Kretschmer <akretschmer@spamfence.net>

Дата:

19 октября 2005 г. в 10:30:27

Tim Nelson  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

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

19 октября 2005 г. в 11:53:58

Tim Nelson  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

getting around---division by zero on numeric

От:

Tim Nelson <timnelson@phreaker.net>

Дата:

19 октября 2005 г. в 09:31:22

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

От:

Richard Huxton <dev@archonet.com>

Дата:

19 октября 2005 г. в 10:06:06

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

От:

Berend Tober <btober@seaworthysys.com>

Дата:

19 октября 2005 г. в 11:21:34

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

От:

Michael Glaesemann <grzm@myrealbox.com>

Дата:

19 октября 2005 г. в 10:30:35

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

От:

Sean Davis <sdavis2@mail.nih.gov>

Дата:

19 октября 2005 г. в 10:05:53

On 10/19/05 8:26 AM, "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

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

От:

Jerry Sievers <jerry@jerrysievers.com>

Дата:

19 октября 2005 г. в 10:31:24

Tim Nelson  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/