Re: [professionel] Re: division by zero error in a request

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;