Divid 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

SQL Server 2000 Forums

Transact-SQL (2000)

Divid by zero

Author

Topic

clerma
Starting Member

2 Posts

Posted - 2006-10-17 : 11:57:26

I have seen one other message for dividing by zero but can not get it to work. I am having to sume each field then divid and keep getting a divid by zero error, could someone help me?

(Sum(dailyopenage))/(Sum(dailyopen)) AS "Avg Age Open"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-17 : 12:06:52

use CASE to prevent divide by zero error:

case sum(dailyopen)
     when 0 then 0
     else sum(dailyopenage) / sum(dailyopen)
end as "Avg Age Open"

Harsh Athalye
India.
"Nothing is Impossible"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-17 : 13:29:50

Division by zero is undefined, so null is probably the most appropriate value to return.


case
when sum(dailyopen) = 0
then null
else sum(dailyopenage) / sum(dailyopen)
end

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-17 : 13:42:25

I wonder what the mathmatical explination is for 1/0 to be undefined

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-10-17 : 13:49:05

http://en.wikipedia.org/wiki/Division_by_zero

STAR SCHEMAS ARE NOT DATA WAREHOUSES!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-17 : 13:55:09

quote:
Originally posted by X002548

I wonder what the mathmatical explination is for 1/0 to be undefined

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

If you divide 15 by 3 you get 5, so 3 times 5 equals 15.

What number would you multiply 0 by to get 1?

CODO ERGO SUM

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-17 : 14:23:44

[quote]Originally posted by Michael Valentine JonesWhat number would you multiply 0 by to get 1?
[/qu6ote]

SELECT POWER(0,0)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

clerma
Starting Member

2 Posts

Posted - 2006-10-18 : 17:33:27

Thanks for the help that worked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources