Re: query; check for zero and floats

vince(at)weaktight(dot)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