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

[Oisin Glynn]

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:







????

Should I select into a temporary table, then SUM that table?

Thanks,
Bernard

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

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;

smime.p7s
Description: S/MIME Cryptographic Signature