pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedquery; check for zero and floats
3+ messages / 3 participants
[nested] [flat]
* query; check for zero and floats
@ 2006-04-01 06:03 vince@weaktight.com
0 siblings, 2 replies; 3+ messages in thread
From: vince@weaktight.com @ 2006-04-01 06:03 UTC (permalink / raw)
To: pgsql-sql
I'm trying to do a simple query and I'm not sure how to get it to work:
SELECT SUM(x0 + y0 + z0) / SUM(x2 + y2) AS A1, SUM(x1 + y1 + z1) / SUM(x3 + y3)
AS A2
FROM test
Problems:
1. All variables are integers. When it does the division, it returns an
integer, but I want a float. (I've tried numerous things to no avail)
2. If SUM(x2 + y2) == 0; there is a divide by zero error. How do I check and
pass on zeroes?
Thanks,
Vince.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: query; check for zero and floats
@ 2006-04-01 06:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: vince@weaktight.com
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2006-04-01 06:32 UTC (permalink / raw)
To: vince@weaktight.com; +Cc: pgsql-sql
vince@weaktight.com writes:
> I'm trying to do a simple query and I'm not sure how to get it to work:
> SELECT SUM(x0 + y0 + z0) / SUM(x2 + y2) AS A1, SUM(x1 + y1 + z1) / SUM(x3 + y3)
> AS A2
> FROM test
> 1. All variables are integers. When it does the division, it returns an
> integer, but I want a float. (I've tried numerous things to no avail)
Cast one or both division inputs to float.
> 2. If SUM(x2 + y2) == 0; there is a divide by zero error. How do I check and
> pass on zeroes?
Add a HAVING condition (not WHERE, because you need to filter on the
post-aggregation status).
I think you want
SELECT SUM(x0 + y0 + z0)::float / SUM(x2 + y2) AS A1,
SUM(x1 + y1 + z1)::float / SUM(x3 + y3) AS A2
FROM test
HAVING SUM(x2 + y2) <> 0
(maybe also having SUM(x3 + y3) <> 0)
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: query; check for zero and floats
@ 2006-04-01 06:34 Joe Conway <mail@joeconway.com>
parent: vince@weaktight.com
1 sibling, 0 replies; 3+ messages in thread
From: Joe Conway @ 2006-04-01 06:34 UTC (permalink / raw)
To: vince@weaktight.com; +Cc: pgsql-sql
vince@weaktight.com wrote:
> I'm trying to do a simple query and I'm not sure how to get it to work:
>
> SELECT SUM(x0 + y0 + z0) / SUM(x2 + y2) AS A1, SUM(x1 + y1 + z1) / SUM(x3 + y3)
> AS A2
> FROM test
>
> Problems:
>
> 1. All variables are integers. When it does the division, it returns an
> integer, but I want a float. (I've tried numerous things to no avail)
>
> 2. If SUM(x2 + y2) == 0; there is a divide by zero error. How do I check and
> pass on zeroes?
A bit ugly, but seems to work:
create table t1 (g int, x0 int, y0 int, z0 int, x2 int, y2 int);
insert into t1 values (0, 1,1,1,1,-1);
insert into t1 values (0, 1,1,1,-1,1);
insert into t1 values (1,1,1,1,1,1);
insert into t1 values (1,2,3,4,5,6);
SELECT g, case
when SUM(x2::float8 + y2::float8) = 0 then
0
else
SUM(x0::float8 + y0::float8 + z0::float8) /
SUM(x2::float8 + y2::float8)
end
AS A1 from t1 group by g;
g | a1
---+-------------------
1 | 0.923076923076923
0 | 0
(2 rows)
HTH,
Joe
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-04-01 06:34 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-01 06:03 query; check for zero and floats vince@weaktight.com
2006-04-01 06:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-01 06:34 ` Joe Conway <mail@joeconway.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox