asked
divide by zero error
I have a t_SQL view that is falling over on a divide by error message during aggrigation. Intrestingly it works fine as a pass through query in MS Access. This is what I have Avg(Stock)/Avg(Volume) if Volume is ZERO or NULL then I get an error so I rewrote it thus COALESCE (ROUND(AVG(Stock) / AVG(NULLIF (Volume, 0)), 1), 0) but I still get this error Msg 8134, Level 16, State 1, Line 2 Divide by zero error encountered. Warning: Null value is eliminated by an aggregate or other SET operation. This is my full SQL code apologies in advance if it's poor. SELECT dbo.vw_ActivesalesData.Chain + ' (' + dbo.vw_ActivesalesData.Country + ') - YTD ' + CAST(dbo.vw_ActivesalesData.Year AS CHAR(4)) + ' week ' + CAST(dbo.vw_ActivesalesData.Week AS CHAR) AS ID, dbo.vw_ActivesalesData.Planogram, dbo.vw_ActivesalesData.ShortCode AS [Short Code], dbo.vw_ActivesalesData.SYMBOL AS Family, dbo.vw_ActivesalesData.Contents, dbo.vw_ActivesalesData.type, dbo.vw_ActivesalesData.HPP, dbo.vw_ActivesalesData.[RRP W/O TAX by Week] AS RRP, ROUND(SUM(dbo.vw_ActivesalesData.Volume) * dbo.vw_ActivesalesData.[RRP W/O TAX by Week], 0) AS [RRP sales value], ROUND(SUM(dbo.vw_ActivesalesData.Volume), 0) AS Packs, ROUND(SUM(dbo.vw_ActivesalesData.Volume) * dbo.vw_ActivesalesData.Size, 0) AS [Cartridge Units], ROUND(AVG(dbo.vw_ActivesalesData.Volume), 2) AS [Weekly average sales volume (packs)], ROUND(AVG(dbo.vw_ActivesalesData.Volume) * dbo.vw_ActivesalesData.[RRP W/O TAX by Week], 2) AS [Weekly average revenue per pack], ROUND(AVG(dbo.vw_ActivesalesData.Stock), 2) AS [Average Weekly stock (packs)], ROUND(AVG(dbo.vw_ActivesalesData.Stock) * dbo.vw_ActivesalesData.[RRP W/O TAX by Week], 2) AS [Weekly average stock at RRP], COALESCE (ROUND(AVG(dbo.vw_ActivesalesData.Stock) / AVG(NULLIF (dbo.vw_ActivesalesData.Volume, 0)), 1), 0) AS [Weeks of Stock], dbo.vw_StoreCountByChain.[Active Stores] FROM dbo.vw_StoreCountByChain CROSS JOIN dbo.vw_ActivesalesData WHERE (dbo.vw_ActivesalesData.Planogram <> 'Z') GROUP BY dbo.vw_ActivesalesData.Planogram, dbo.vw_ActivesalesData.ShortCode, dbo.vw_ActivesalesData.SYMBOL, dbo.vw_ActivesalesData.Contents, dbo.vw_ActivesalesData.type, dbo.vw_ActivesalesData.HPP, dbo.vw_ActivesalesData.[RRP W/O TAX by Week], dbo.vw_StoreCountByChain.[Active Stores], dbo.vw_ActivesalesData.Chain, dbo.vw_ActivesalesData.Country, dbo.vw_ActivesalesData.Year, dbo.vw_ActivesalesData.Week, dbo.vw_ActivesalesData.Size, dbo.vw_StoreCountByChain.[Active Stores]
This site works by you voting. For all helpful answers, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark.
I'm not sure that your problem lies in this query. You have only one divide operation, which executes just fine whether volume is null or zero: create table #t (Id int identity, Stock int, Volume int) insert #t (Stock,Volume) select 21,5 union all select 33,4 union all select 67,32 union all select 12,7 union all select 128,8 union all select 128,0 union all select 23,null select Id, COALESCE (ROUND(AVG(Stock) / AVG(NULLIF (Volume, 0)), 1), 0) from #t group by Id /* Id (No column name) 1 4 2 8 3 2 4 1 5 16 6 0 7 0 */ Could your problem be further up the chain? I.e. the view (or views) that you're querying? Can you do a simple select against each of the views that you are using?
There was a deep buried error further up the chain, thanks for pointing out that there was no obvious problem with this query. I felt better for that.
Can you try somethihng like this declare @z int = null SELECT 1/ (CASE (@z) WHEN 0 THEN 1 WHEN null THEN 1 ELSE @z END)
This solution is not correct since replacing NULL or ZERO with 1(ONE) will change the overall average computation.
I can't see why it gives you a divide by zero error but it's a complicated statement. Try starting with the lowest level parts: SELECT ... , AVG(dbo.vw_ActivesalesData.Stock) as Part1 , AVG(NULLIF (dbo.vw_ActivesalesData.Volume, 0)) AS Part2 FROM ... Then build it up again one piece at a time until you see where it's going wrong.
18 People are following this question.
