[EMAIL PROTECTED] 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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
