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

On 10/19/05 8:26 AM, "Tim Nelson" <timnelson(at)phreaker(dot)net> 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

Can you use case?

http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html

Or you could create a simple function to do the logic to avoid the division
by zero.

Sean