Re: division by zero error in a request

Bernard Grosperrin wrote:
> I wants to make a view giving me some statistics.
>
> I am not sure to understand why something like this
>
> SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) /
> (sold_parts_amount_dly + sold_labor_amount_dly) from sales
>
> give me a division by zero error?
>
> If that is not the way to go, should I write a function that I would call
> instead?
>
> Thanks,
> Bernard
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
If (sold_parts_amount_dly + sold_labor_amount_dly) equals zero you will
get an error as you cannot divide by zero.

Also in every case where (sold_parts_amount_dly + sold_labor_amount_dly)
is not zero wont the answer be 1 as

(sold_parts_amount_dly + sold_labor_amount_dly)/(sold_parts_amount_dly + sold_labor_amount_dly) is always 1??

To try and find the zero you could do the following:

select count(*) from sales where (sold_parts_amount_dly +
sold_labor_amount_dly)=0;

and if that is not a staggering amount of rows
select * from sales where (sold_parts_amount_dly + sold_labor_amount_dly)=0;
and try and identify why this is happening if it is not expected?

or if you want to ignore rows where (sold_parts_amount_dly +
sold_labor_amount_dly)=0;
then

SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) /
(sold_parts_amount_dly + sold_labor_amount_dly) from sales
where (sold_parts_amount_dly + sold_labor_amount_dly)>0

Oisin