Thread: division by zero error in a request

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

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

Bernard Grosperrin wrote:
> Oisin
>> 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
> Thanks for your answer.
>
> The real request would be something like this:
>
> SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) -
> cost_amount_dly /
> (sold_parts_amount_dly + sold_labor_amount_dly) from sales where
> (sold_parts_amount_dly + sold_labor_amount_dly)>0
>
> My problem is that in fact I want to SUM those amounts, with a GROUP
> BY per location. But by doing so, any location where one row has where
> = 0 is eliminated. So, is there a way to SUM inside a subset returned by
>
> SELECT location_id, (sold_parts_amount_dly + sold_labor_amount_dly) -
> cost_amount_dly /
> (sold_parts_amount_dly + sold_labor_amount_dly) from sales where
> (sold_parts_amount_dly + sold_labor_amount_dly)>0
>
> ????
>
> Should I select into a temporary table, then SUM that table?
>
> Thanks,
> Bernard
>
Please always copy the list on your responses as others may be interested.
Something like this should work. I am not sure how inefficient it is.

select location_id, SUM((sold_parts_amount_dly + sold_labor_amount_dly)
- cost_amount_dly) /
SUM(sold_parts_amount_dly + sold_labor_amount_dly) from sales s1  where
(select  SUM(sold_parts_amount_dly + sold_labor_amount_dly) from sales
s2 where s2.location_id = s1.location_id) > 0 group by location_id;