Divide by Zero Error Encountered

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Divide by Zero Error Encountered

Author

Topic

manu
Starting Member

41 Posts

Posted - 2006-01-09 : 08:41:16

Hi,

i am getting some problem sometimes (Divide by Zero Error Encountered)
when i run the query. some times it works when i run it for different parameters.

Pls help me in solving the problem.

Munish Gupta

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2006-01-09 : 08:43:01

post the query here

Duane.

"It's a thankless job, but I've got a lot of Karma to burn off."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-09 : 08:43:47

The column has zero values

Do you want to skip if the column value is 0?

Post the query you used

Madhivanan

Failing to plan is Planning to fail

manu
Starting Member

41 Posts

Posted - 2006-01-09 : 09:03:13

select agentlogin, max(agentgivenname),
day(timestamp), convert(varchar(20),
dateadd(s, (sum(talktime)-(sum(shortcallsanswered)*10))/(sum(callsanswered)-sum(shortcallsanswered)), '19000101'), 108) as avgtalktime
from iagentperformancestat
where day(timestamp) between 7 and 7 and month(timestamp) = 1 and
year(timestamp) = 2006 and agentlogin > 1250
group by day(timestamp), agentlogin
order by agentlogin

yes sir, i want to skip the column if the value is null

Munish Gupta

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-09 : 09:45:25

Try this

select agentlogin, max(agentgivenname),
day(timestamp), convert(varchar(20),
dateadd(s, (sum(talktime)-(sum(shortcallsanswered)*10))/(sum(callsanswered)-sum(shortcallsanswered)), '19000101'), 108) as avgtalktime
from iagentperformancestat
where day(timestamp) between 7 and 7 and month(timestamp) = 1 and
year(timestamp) = 2006 and agentlogin > 1250
group by day(timestamp), agentlogin
having sum(callsanswered)-sum(shortcallsanswered)<>0
order by agentlogin

Madhivanan

Failing to plan is Planning to fail

manu
Starting Member

41 Posts

Posted - 2006-01-09 : 09:54:57

Thanks Sir,
it is working now.

AS i have got it, we should not divide it by zero

Munish Gupta

MSSqlLearner
Starting Member

2 Posts

Posted - 2008-07-10 : 13:58:40

quote:
Originally posted by madhivanan

Try this

select agentlogin, max(agentgivenname),
day(timestamp), convert(varchar(20),
dateadd(s, (sum(talktime)-(sum(shortcallsanswered)*10))/(sum(callsanswered)-sum(shortcallsanswered)), '19000101'), 108) as avgtalktime
from iagentperformancestat
where day(timestamp) between 7 and 7 and month(timestamp) = 1 and
year(timestamp) = 2006 and agentlogin > 1250
group by day(timestamp), agentlogin
having sum(callsanswered)-sum(shortcallsanswered)<>0
order by agentlogin

Madhivanan

Failing to plan is Planning to fail

Mr.Madhivanan:

I can see that if you use the sum function. what if you don't use the sum function? How would you get rid of this 0 error encounter?

Thank you in advance.

cvipin
Yak Posting Veteran

51 Posts

Posted - 2008-07-10 : 14:18:18

I think if you use having clause you will lose some records when sum(callsanswered) = sum(shortcallsanswered). If you still want to display these records, you can use something like below:

select agentlogin, max(agentgivenname),
day(timestamp), convert(varchar(20),
CASE WHEN sum(callsanswered) <> sum(shortcallsanswered) THEN dateadd(s, (sum(talktime)-(sum(shortcallsanswered)*10))/(sum(callsanswered)-sum(shortcallsanswered)), '19000101'), 108) ELSE 0 END as avgtalktime
from iagentperformancestat
where day(timestamp) between 7 and 7 and month(timestamp) = 1 and
year(timestamp) = 2006 and agentlogin > 1250
group by day(timestamp), agentlogin
-- having sum(callsanswered)-sum(shortcallsanswered)<>0 order by agentlogin

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-11 : 13:57:26

EDIT: Sorry I didn't realize I was in the SQL 2000 forum.I thought I was in the 2005 forum. The code below doesn't cause an error in SQL 2000 but does in 2005. Please ignore.

Also, you cannot depend on the order in which SQL will evaluate certain things. In this case the HAVING clause is not guaranteed to by applied before the division so you may or may not get a division by zero. Here are some examples (yes this is a contrived sample :) ):

DECLARE @Yak TABLE (ID INT, Val INT)

INSERT @Yak
SELECT 1, 0
UNION ALL SELECT 2, 1
UNION ALL SELECT 2, 5
UNION ALL SELECT 2, 10


-- Works as expected
SELECT
	ID,
	SUM(Val) / (COUNT(ID) - 1) AS MyNum
FROM	
	@Yak
GROUP BY
	ID
HAVING 
	COUNT(ID) > 1


-- Division by Zero
SELECT
	ID
FROM
	(
		SELECT
			ID,
			SUM(Val) / (COUNT(ID) - 1) AS MyNum
		FROM	
			@Yak
		GROUP BY
			ID
		HAVING 
			COUNT(ID) > 1
	) AS T
WHERE
	T.MyNum > 1


-- Solution without having
SELECT
	ID
FROM
	(
		SELECT
			ID,
			COALESCE(SUM(Val) / NULLIF((COUNT(ID) - 1), 0), 0) AS MyNum,
			COUNT(*) AS MyCount
		FROM	
			@Yak
		GROUP BY
			ID
	) AS T
WHERE
	T.MyNum > 1
	AND MyCount > 1

-- And with Having
SELECT
	ID
FROM
	(
		SELECT
			ID,
			COALESCE(SUM(Val) / NULLIF((COUNT(ID) - 1), 0), 0) AS MyNum
		FROM	
			@Yak
		GROUP BY
			ID
		HAVING 
			COUNT(ID) > 1
	) AS T
WHERE
	T.MyNum > 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources