Divide by zero error encountered......

Question

Divide by zero error encountered......

b_boy

SSCertifiable

Points: 5145
More actions
September 24, 2008 at 5:21 am

#198386

Hello
I am using the following query:
set rowcount 20
select
datename(MM,t.OrderDate) as 'Month',
i.ProductClassID,
count(tl.orderid)as [Orders In], sum(tl.[Unit Price] *tl.Quantity) as [£ Orders In],
sum(case datepart(yy,t.orderdate) when 2007 then 1 else 0 end) as 'Orders 2007',
sum(case datepart(yy,t.orderdate) when 2008 then 1 else 0 end) as 'Orders 2008',
(100.*
sum(case datepart(yy,t.orderdate) when 2008 then 1. else 0. end) /
sum(case datepart(yy,t.orderdate) when 2007 then 1. else 0. end)) - 100. as 'Percent Change'
from t_orderline tl inner join t_items i on tl.StockID = i.stockid inner join t_orderheader t
on tl.orderid = t.orderid
where (t.orderdate between getdate()-365 and getdate()) and datepart(mm,t.OrderDate)<=5
group byi.ProductClassID,
datepart(mm,t.OrderDate),
datename(mm,t.OrderDate)with rollup
havingdatepart(mm,t.OrderDate)is null or
datename(mm,t.OrderDate) is not null
set rowcount 0
--
And i get the following error message:
Msg 8134, Level 16, State 1, Line 5
Divide by zero error encountered.
--
can anyone help?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

b_boy (9/24/2008)
(100.*
sum(case datepart(yy,t.orderdate) when 2008 then 1. else 0. end) /
sum(case datepart(yy,t.orderdate) when 2007 then 1. else 0. end)) - 100. as 'Percent Change'
from t_orderline tl inner join t_items i on tl.StockID = i.stockid inner join t_orderheader t
on tl.orderid = t.orderid
where (t.orderdate between getdate()-365 and getdate()) and datepart(mm,t.OrderDate)<=5
group byi.ProductClassID,
datepart(mm,t.OrderDate),
datename(mm,t.OrderDate)with rollup
havingdatepart(mm,t.OrderDate)is null or
datename(mm,t.OrderDate) is not null
set rowcount 0

The second part of the division

sum(case datepart(yy,t.orderdate) when 2007 then 1. else 0. end)

will be 0 if orderdate not in 2007. Thus you get an error "division by zero".

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Hi b_boy

The part in bold is evaluating to zero for some rows:

(100.*

sum(case datepart(yy,t.orderdate) when 2008 then 1. else 0. end) /

sum(case datepart(yy,t.orderdate) when 2007 then 1. else 0. end)) - 100.

You have several options. The easiest is to wrap the whole expression in a CASE, checking for the divisor evaluating to zero and setting the whole expression to zero if it does. The downside is that the expression will become complex and difficult to maintain.

The next easiest is to use a function for the arithmetic, in which the divisor is checked for zero. Finally, you could change this query to an "onion" query i.e. perform aggregates first to a derived table, then wrap this in an outer select for the final calculations such as percentage.

I use a function if the number of rows is small, and almost always use an "onion" select if the function impacts on performance. Onion selects usually have minimal affect on performance and make the code much easier to read and maintain.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 3

Try using

(100.*

sum(case datepart(yy,t.orderdate) when 2008 then 1. else 0. end) /

NULLIF(sum(case datepart(yy,t.orderdate) when 2007 then 1. else 0. end),0) - 100.0) as 'Percent Change'

Madhivanan

Failing to plan is Planning to fail