question

CoffeeGuru avatar image
CoffeeGuru 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]
t-sqlaggregatesviews
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
iainrobertson avatar image
iainrobertson answered
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?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
0 Likes 0 ·
sksql avatar image
sksql answered
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)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This solution is not correct since replacing NULL or ZERO with 1(ONE) will change the overall average computation.
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.