Message-ID: <442E1EDA.6040402@joeconway.com>
Date: Fri, 31 Mar 2006 22:34:02 -0800
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.7.12) Gecko/20060202 Fedora/1.7.12-1.5.2
MIME-Version: 1.0
To: vince@weaktight.com
CC: pgsql-sql@postgresql.org
Subject: Re: query; check for zero and floats
References: <20060401010317.tbza82opw0c0ccg0@webmail.weaktight.com>
In-Reply-To: <20060401010317.tbza82opw0c0ccg0@webmail.weaktight.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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