Msg 8134, Level 16, State 1, Line 1,Divide by zero error encountered.,Warning: Null value is eliminated by an aggregate or other SET operation.

Question

Msg 8134, Level 16, State 1, Line 1,Divide by zero error encountered.,Warning: Null value is eliminated by an aggregate or other SET operation.

klineandking

SSCrazy

Points: 2570
More actions
January 27, 2010 at 10:53 am

#214896

good morning guys here is my code and the error its getting
SELECT D.SellerName as Seller,
F.IsAuctioned as Actn,
F.IsSold as Sold,
NULLIF(F.IsSold,0) / NULLIF(F.IsAuctioned,0) as 'Sold Pct',
F.TotalAmount as 'Sold Amt',
NULLIF(F.TotalAmount,0) / NULLIF(F.IsSold,0) as 'Sold Avg',
F.IsSubjectToApproval as 'Sold ST',
F.IsPending as Pend,
Count(F.ApprovedDate) as 'Acpt',
SUM(F.Isrejected) as 'Rjct',
NULLIF(F.Ispending,0)/NULLIF(F.IsSubjectToApproval,0)*100 as 'Pend %',
(COUNT(F.ApprovedDate)/COUNT(Nullif(F.IsSubjectToApproval,0))) * 100 as 'Acpt %',
SUM(NULLIF(F.IsRejected,0)/NULLIF(F.IsSubjectToApproval,0)) * 100 as 'Rjct %',
SUM(NULLIF(F.IsRejected,0)/NULLIF(F.IsSold, 0))as 'Rjct Sold',
CASE WHEN F.IsPending = 1 THEN F.TotalAmount ELSE NULL END as 'Pend Amt',
CASE WHEN F.ApprovedDate IS not null THEN F.TotalAmount ELSE null end as 'Acpt Amt',
CASE WHEN F.IsRejected = 1 THEN F.TotalAmount ELSE NULL End AS 'Rjct Amt',
CASE WHEN F.ApprovedDate IS not null THEN AVG(NULLIF(F.WinningBid,0)/NULLIF(F.AuctionSellerReserve, 0))*100 ELSE NULL END as 'Avg',
CASE WHEN F.ApprovedDate IS not null THEN MAX(NULLIF(F.WinningBid,0)/NULLIF(F.AuctionSellerReserve, 0))*100 ELSE NULL END as 'Max',
CASE WHEN F.ApprovedDate IS not null THEN MIN(NULLIF(F.WinningBid,0)/NULLIF(F.AuctionSellerReserve, 0))*100 ELSE NULL END as 'Min',
CASE WHEN F.IsRejected IS not null THEN AVG(NULLIF(F.Winningbid,0)/NULLIF(F.AuctionSellerReserve, 0))* 100 ELSE NULL END as 'Avg',
CASE WHEN F.IsRejected IS not null THEN MAX( NULLIF(F.Winningbid,0)/NULLIF(F.AuctionSellerReserve, 0))* 100 ELSE NULL END as 'Max',
CASE WHEN F.IsRejected IS not null THEN MIN(NULLIF(F.Winningbid,0)/NULLIF(F.AuctionSellerReserve, 0))* 100 ELSE NULL END as 'Min'
FROM EDW.dbo.FactResidential F
INNER JOIN EDW.dbo.DimSeller D
ON F.SellerId = D.SellerId
GROUP BY D.SellerName,
F.IsAuctioned,
F.IsSold,
NULLIF(F.IsSold,0)/NULLIF(F.IsAuctioned,0),
F.TotalAmount,
NULLIF(F.TotalAmount,0)/NULLIF(F.IsSold,0),
F.IsSubjectToApproval,
F.IsPending,
F.ApprovedDate,
F.IsRejected
the error is Msg 8134, Level 16, State 1, Line 1,Divide by zero error encountered.,Warning: Null value is eliminated by an aggregate or other SET operation.
how do i eliminate the error thanks

Viewing 15 posts - 1 through 15 (of 16 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

It would help to have the table definition(s) (CREATE TABLE statement(s)), sample data (as a series of INSERT INTO statements) for the table(s), expected results based on sample data. Be sure the sample data reflects the problems that you are attempting to correct.

LutzM SSC Guru Points: 107049 More actions · Answer 2

Maybe it's the wrong function you're using:

AVG(NULLIF(F.WinningBid,0)/NULLIF(F.AuctionSellerReserve, 0))

Straight from BOL (BooksOnLine):

NULLIF returns the first expression if the two expressions are not equivalent. If the expressions are equivalent, NULLIF returns a null value of the type of the first expression.

I guess you're looking for ISNULL() function...

If so, I would change the divisor part to ISNULL(F.AuctionSellerReserve, 1) to avoid divsion by Zero.

If those few hints won't help you should follow Lynns suggestion regarding some data to play with.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Actually, you only need the NULLIF in denominator:

AVG(ISNULL(F.WinningBid/NULLIF(F.AuctionSellerReserve, 0), 0))

Try making a change similar to the above in your code.

myheadhurts Ten Centuries Points: 1262 More actions · Answer 4

If F.IsSubjectToApproval = 0, then this call:

...COUNT(Nullif(F.IsSubjectToApproval,0)...

Returns 0 ...KABLOOIE!

-MarkO

"You do not really understand something until you can explain it to your grandmother" - Albert Einstein

klineandking SSCrazy Points: 2570 More actions · Answer 5

klineandking

SSCrazy

Points: 2570

January 27, 2010 at 11:39 am

#1109482

Thanks ll try it i appreciate your answer

klineandking SSCrazy Points: 2570 More actions · Answer 6

i think it works if i run into any more problems you ll be the first to know thanks i appreaciate it i wish i could buy you a round of beer for your answer am in riverside where are you

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

A) I don't drink beer.

B) If Riverside is CA, I happen to be in CO.

But it is the thought that counts, thanks.

myheadhurts Ten Centuries Points: 1262 More actions · Answer 8

I believe your use of NULLIF(...) is valid here.

It's the call to COUNT(...) that can return a 0.

Why have that call to COUNT() there?

-MarkO

"You do not really understand something until you can explain it to your grandmother" - Albert Einstein

myheadhurts Ten Centuries Points: 1262 More actions · Answer 9

This illustrates the situ.

select count(Nullif(0,0))

-MarkO

"You do not really understand something until you can explain it to your grandmother" - Albert Einstein

LutzM SSC Guru Points: 107049 More actions · Answer 10

Seems like I totally missed the point. Sorry about that... :blush:

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

lmu92 (1/27/2010)
Seems like I totally missed the point. Sorry about that... :blush:

Okay, I'm lost on how you are lost. Please, what did you miss? It was your post that I based my response on that seemed to fix the problem.

myheadhurts Ten Centuries Points: 1262 More actions · Answer 12

Run this:

Select 1/Nullif(0,0)

Then run this:

Select 1/count(Nullif(0,0))

-MarkO

"You do not really understand something until you can explain it to your grandmother" - Albert Einstein

klineandking SSCrazy Points: 2570 More actions · Answer 13

thanks for your answer based on the query how would i make the result set to group on the D.sellername

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

klineandking (1/27/2010)
thanks for your answer based on the query how would i make the result set to group on the D.sellername

At this point, go back and read my first post. Need more info to help further.