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

On Oct 19, 2005, at 21:26 , Tim Nelson wrote:

> 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

You might try a CASE expression like so:


select type
     , sum(sales) as sales_total
     , sum(cost) as cost_total
     , case when sum(sales) <> 0
     then (sum(sales) * sum(cost)/sum(sales)) * 100
     else 0
     end as calculation
from test
group by type;

I don't know what you want as a result when sum(sales) = 0; I just
put 0 in because you'll need a numeric result, (unless you cast to
text, for example, if you wanted to use '--' or '').

As an aside, but it's generally considered good practice to refer to
attributes by name rather than position. Also, while SQL does not
require it, a relation should have unique attribute names, which is
why I've aliased the attributes of the result. Otherwise you'll have
two attributes named "sum" (and another named "case", iirc, which
isn't really very descriptive).

Hope this helps.


Michael Glaesemann
grzm myrealbox com