Обсуждение: 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;