prevention of divide 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)

prevention of divide by Zero

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-03-13 : 14:22:17

Hi,

In my select statement, i'm doing a divide and sometimes i'm getting an 'divide by zero' error.

SUM(s.Leads)/ SUM(s.Sales) as CTR

How can I prevent this?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-13 : 14:35:36

You will have to treat 0 as a special case
....
SUM(s.Leads)/ SUM(s.Sales) as CTR
....
HAVING SUM(s.Sales) <> 0

rockmoose

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-03-13 : 14:42:01

You can also use a CASE statement if you want to include the zero results.

DECLARE @int1 DECIMAL(5,3), @int2 DECIMAL(5,3)

SELECT @int1 = 1, @int2 = 2
SELECT CASE WHEN @int2 = 0 THEN 0 ELSE SUM(@int1)/SUM(@int2) END

SELECT @int1 = 1, @int2 = 0
SELECT CASE WHEN @int2 = 0 THEN 0 ELSE SUM(@int1)/SUM(@int2) END

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-13 : 15:36:30

so since when is x/0 = 0 ???

just being a pia

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-03-13 : 16:07:12

Gee, now we're getting into philosophical questions of what a zero is.

I believe it directly correlates to
DBA/Developer = ahhhh, nevermind.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2005-03-13 : 16:21:45

actually what's happening is that one of the numbers gets updated in real-time, while the other is cached...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-03-13 : 16:37:19

Urrrr? Houston, we have a problem!!!

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-13 : 16:43:10

I usually return NULL when dividing by zero, and at the presentation layer I display "N/A" or something like that.

- Jeff

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-03-13 : 16:46:33

Presentation layer???? What's that?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-13 : 16:58:34

Since the result of a divide by zero is undefined, how about this?

	case
	when SUM(s.Sales) <> 0
	then SUM(s.Leads)/SUM(s.Sales)
	else NULL
	end
	as CTR

It really depends on what the application needs. You are computing something like leads needed per sale, so a zero would be very misleading, since it implies that you don't need any leads to get a sale.

Logically, infinity would be a better answer since that is the limit that SUM(s.Leads)/SUM(s.Sales) approaches as the value of SUM(s.Sales) approaches zero. Good luck coding that!

CODO ERGO SUM

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-15 : 14:15:14

quote:
Originally posted by derrickleggett

Presentation layer???? What's that?

Maybe it's a technical term for when the Presentation is over and you Lay your cards on the table for the final offering:
"your place, or mine?"

rockmoose

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-15 : 14:20:50

ROTFL!!!

so rockmoose, how many of the "presentees" took you up on the offer?

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources