Re: [SQL] query; check for zero and floats
[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
Re: [SQL] query; check for zero and floats
[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] query; check for zero and floats
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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] have you feel anything when you read this ?
On Fri, 31 Mar 2006, Eugene E. wrote: > Peter Eisentraut wrote: > > Eugene E. wrote: > > > >>the problem is: you'll get this four byte sequence '\000' _instead_ > >>of NUL-byte anyway. > > > > > > What you seem to be missing is that PostgreSQL data can be represented > > in textual and in binary form. What you in psql is the textual form. > > If you want the binary form you need to select it. Then you can pass > > the exact bytes back and forth. > > your sentence is not true. > I can not select exact bytes even if i use BYTEA type No, that is still using the textual form. If you use PQexecParams and set the last argument to show you want binary data, you should get binary data. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
