devision by zero - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

devision by zero

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-30 : 20:34:47

I have a division by zero, caused by this expression which I thought was trouble-proved:

COALESCE(SUM(TiempoS)*100/NULLIF(sum(LP_KAPA_DAY_HOURS*LP_KAPA_DAY_WORKER_QTY*120)/COUNT(ORDEN),0),0)

It worked correctly but now that I added the Count() Devision it stops with the devision by zero error.

What am I not seeing?
Martin

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-30 : 20:44:25

premature posting...

COALESCE(SUM(TiempoS)*100/NULLIF(sum(LP_KAPA_DAY_HOURS*LP_KAPA_DAY_WORKER_QTY*120),0)/NULLIF(COUNT(ORDEN),0),0)

works

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-30 : 23:01:28

Well of course it can give you a /0 if your count(ORDEN) evaluates to 0 - i.e. there are no non-null ORDEN in your group.
What would you expect it to do? Perhaps you mean count(*) - the count for the group?
Depending on your grouping, it looks a bit like you might be looking for AVG in there somewhere too.
Oh, and count() can never return NULL so your NULLIF is not required.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-31 : 08:09:26

quote:
Originally posted by LoztInSpace
Oh, and count() can never return NULL so your NULLIF is not required.

Isn't that the reason why you DO need to use NULLIF if you want to avoid division by zero? So this seems right to me:

NULLIF(COUNT(ORDEN),0)

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-31 : 09:17:26

No, given that the expression is used as a divisor, that just means you divide by zero in the impossible case your count returns NULL doesn't it?

COALESCE(SUM(TiempoS)*100/NULLIF(sum(LP_KAPA_DAY_HOURS*LP_KAPA_DAY_WORKER_QTY*120),0)/NULLIF(COUNT(ORDEN),0),0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 09:44:57

I also think there should be NULLIF(cnt,0) in denominator for the same explanation that count can never return NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-31 : 09:47:34

I might be thinking about a different scenario that what you are thinking. What I am thinking is the following situation:

CREATE TABLE #tmp (ORDEN FLOAT);
INSERT INTO #tmp VALUES (NULL),(NULL);

-- I don't want this - which causes a divide by zero error and 
-- terminates the batch (depending on XACT_ABORT setting)
SELECT 25.0/COUNT(ORDEN) FROM #tmp;

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.


-- I would rather get this, take the NULL as an indicator that 
-- I don't have a valid divisor and proceed accordingly.
SELECT 25.0/NULLIF(COUNT(ORDEN),0) FROM #tmp;


DROP TABLE #tmp;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-31 : 09:48:34

quote:

Originally posted by sunitabeck

I might be thinking about a different scenario that what you are thinking. What I am thinking is the following situation:

CREATE TABLE #tmp (ORDEN FLOAT);
INSERT INTO #tmp VALUES (NULL),(NULL);

-- I don't want this - which causes a divide by zero error and 
-- terminates the batch (depending on XACT_ABORT setting)
SELECT 25.0/COUNT(ORDEN) FROM #tmp;

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.


-- I would rather get this, take the NULL as an indicator that 
-- I don't have a valid divisor and proceed accordingly.
SELECT 25.0/NULLIF(COUNT(ORDEN),0) FROM #tmp;


DROP TABLE #tmp;

Exactly reason for which i would use the same

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-07-31 : 10:09:15

You are both correct. I was reading NULLIF as ISNULL. Excuse my brain-fade and resulting confusion that hasn't really helped anyone!
Oops!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources