Re: getting around---division by zero on numeric

Tim Nelson <timnelson(at)phreaker(dot)net> 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/