Postgres Pro
Downloads
Home   >   mailing lists

Thread: query; check for zero and floats

query; check for zero and floats

From
vince@weaktight.com
Date:
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.

Re: query; check for zero and floats

From
Tom Lane
Date:
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

Re: query; check for zero and floats

From
Joe Conway
Date:
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