Tim Nelson <timnelson@xxxxxxxxxxxx> writes:
> I am getting division by zero on a calculated field ( sum(sales) is
> 0 ) and I can't find a way around this. I figured out you can't use
> an aggregate in a where, and using having the parser must
> (obviously) evaluate the select fields before considering teh having
> clause.
>
> Does anyone have a way around this? Thanks!
>
> select
> type,
> sum(sales),
> sum(cost),
> (sum(sales) * sum(cost) / sum(sales)) * 100
> from test
> group by 1
> having sum(sales) != 0
Suggest using a nested query approach;
select
a,
b/c as result
from (
select
a,
sum(b) as b,
sum(c) as c
from foo
group by a
having (sum(c) != 0
)
as inner
;
Prevents the division operation from seeing a 0 and avoids the problem
HTH
--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
