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

Tim Nelson <timnelson@phreaker.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/