From: Tom Lane <tgl@sss.pgh.pa.us>
To: vince@weaktight.com
Cc: pgsql-sql@postgresql.org
Subject: Re: query; check for zero and floats
Date: Sat, 01 Apr 2006 01:32:39 -0500
Message-ID: <8347.1143873159@sss.pgh.pa.us> (raw)
In-Reply-To: <20060401010317.tbza82opw0c0ccg0@webmail.weaktight.com>
References: <20060401010317.tbza82opw0c0ccg0@webmail.weaktight.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, vince@weaktight.com
Subject: Re: query; check for zero and floats
In-Reply-To: <8347.1143873159@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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