asked on

Divide by zero error encountered - In SELECT GROUP BY

I have this SELECT statement in a stored procedure :

SELECT
eng_name AS Name,
DATEPART(YEAR,TimexDate) AS 'Year',
DATEPART(MONTH,TimexDate) AS 'Month',
EngineerID,
SUM(Minutes_Elapsed) AS Minutes_Elapsed_Total,
SUM(Minutes_RW) AS Minutes_RW,
SUM(NoOfBreakdown) AS NoOfBreakdown,
SUM(CAST(Minutes_RW AS Decimal(7,2)))/ SUM(CAST(NoOfBreakdown AS Decimal(7,2))) AS Minutes_RW_Average

What I am trying to workout the average time spend on each breakdown. However I am getting this error message when I execute the stored procedure :

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

(1 row(s) affected)

The procedure only started to fail when I added the final line of the SELECT statement.

Vitor Montalvão

Looks like you have NoOfBreakdown with zero value and that's why you're facing the error.
You can avoid that by testing the zero value but then, what do you want to do when it's zero?
For example, if you want to return zero in those cases:

SUM(CASE NoOfBreakdown
	WHEN 0 THEN 0
	ELSE CAST(Minutes_RW AS Decimal(7,2))/ SUM(CAST(NoOfBreakdown AS Decimal(7,2))
) AS Minutes_RW_Average

Open in new window

Vitor Montalvão

Sorry, this is the correct formula:

SUM(CAST(Minutes_RW AS Decimal(7,2)))
	/ 
SUM(CASE NoOfBreakdown
	WHEN 0 THEN 0
	ELSE CAST(NoOfBreakdown AS Decimal(7,2))
END)
AS Minutes_RW_Average

Open in new window

CMChalcraft

ASKER

Hi,

Thanks for this.

I tried the formula and still got the same error message. I think because I also have some zero values in the field 'Minutes_RW'.

So I amended your formula to test for Zero value in Minutes_RW. Still got the error message.

Then I said if Zero in NoOfBreakdown then use 0.00000001 and it works. I am sure this is not correct programatically but I do get near enough to the result I need today.

SUM(CASE Minutes_RW WHEN 0 THEN 0 ELSE CAST(Minutes_RW AS Decimal(7,2)) END )
       /
SUM(CASE NoOfBreakdown
       WHEN 0 THEN 0.00000001
       ELSE CAST(NoOfBreakdown AS Decimal(7,2))
END)
AS Minutes_RW_Average,

How do I test to ensure that both fields have a value greater than zero and if not then do not divide?

Regards

Chris

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CMChalcraft

ASKER

Very quick and clear reply.

Thanks