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

[Oisin Glynn <me@xxxxxxxxxxxxxx>]

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;

Attachment: smime.p7s
Description: S/MIME Cryptographic Signature