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 2008 Forums

Transact-SQL (2008)

Divide by zero

Author

Topic

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-01-03 : 10:01:32

I have used Computed Column Formula that calculates based on the sum of three other fields. When a user enters 0 into these fields I get a divide by zero error. What I want it to do is populate the calculated with a zero.

The formula I have at the moment is

((([Test1]+[Test2])+[Test3])/(3))

How can I get this to populate the field with zero if the sum of these fields is zero?

Many thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-03 : 10:06:33

hmm..the way you've it wont cause divide by zero error as you've constant in denominator

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-03 : 10:07:26

for making it zero it should be like

isnull(((nullif(([Test1]+[Test2])+[Test3]),0)/(3)) ,0)

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

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-01-03 : 10:08:45

Thanks for the reply. Can you explain that for me. I don't fully understand what you getting at there.

Thanks.

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-01-03 : 10:10:21

ok I just got your second message after I wrote the previous one.
Bear with me a sec and let me test that.

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-01-03 : 10:20:50

I get an error with that, that reads:

Error validating the formula for 'AverageTest'

And if I press save changes I get the following

'Calibration' table
- Unable to modify table.
Incorrect syntax near ')'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-03 : 10:52:35

can you show your full query...I'm sure not posting full part which is why its causing the error with my changes

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

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-01-03 : 11:06:25

(([Test1]+[Test2])+[Test3])/(3)) is the full formula.

This is posted in the field adjacent to (Formul) under the Computed Column Specification.

I copied and pasted what you entered and thats when I got the error.

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-01-03 : 11:23:34

Visakh - I think my error is coming from a view and not the calculated field. Please bear with me whilst I check into this.

Many thanks.

Kristen
Test

22859 Posts

Posted - 2012-01-03 : 11:55:37

As Visakh has said (but I've extracted just that part by way of explanation): put


SomeColumn / NullIf(SomeOtherColumn1 + SomeOtherColumn2, 0)

around the divisor to prevent getting "Divide by Zero" error (you will get NULL instead)

And if you want a ZERO result returned when the calculation is NULL then:


COALESCE(SomeValueThatMightBeNull, 0)

you can use ISNULL instead of COALESCE in this example, there are subtle differences and my preference is for COALESCE

personally I would prefer to have NULL as the output, indicating that there is a problem, rather than ZERO which will hide the problem and not be distinguishable between a real Zero value and a "fake" one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-03 : 11:58:38

quote:
Originally posted by ConfusedAgain

Visakh - I think my error is coming from a view and not the calculated field. Please bear with me whilst I check into this.

Many thanks.

No problem
first check where exactly error is happening

it should be in a place where you've an expression as denominator

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

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-01-05 : 07:15:11

ok I have found out where my error is coming from. There is a view that runs of tables that are populated from the earlier posts. They all work fine but it is the view that falls over if a line on it as the sum total of zero. This is a division by zero error.

SELECT TestID, SUM(TestPercentage) / SUM(CalulateLine) AS TestAverageError
FROM dbo.TestRun
GROUP BY TestID

What I need is if the sum of test percentage is zero is to display zero if not carry out the calculation.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-05 : 07:32:59

What I need is if the sum of test percentage is zero is to display zero if not carry out the calculation.
You are still on wrong way.
SUM(TestPercentage) cannot cause a "Division by Zero" in the given formular but SUM(CalulateLine) can...

SUM(TestPercentage) / NULLIF(SUM(CalulateLine),0)

No, you're never too old to Yak'n'Roll if you're too young to die.

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-01-05 : 07:41:43

Ok I understand it should be (SUM(CalulateLine) indeed thank you. And what you have provided allows the calculation to run without error but it does populate the field with null and what I am after is Zero not null.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-05 : 08:15:24

COALESCE((SUM(TestPercentage) / NULLIF(SUM(CalulateLine),0)),0)

No, you're never too old to Yak'n'Roll if you're too young to die.

ConfusedAgain
Yak Posting Veteran

82 Posts

Posted - 2012-01-05 : 08:25:50

Thank you webfred that is perfect. Really appreciate your help many thanks to you everyone else who contributed.
Im going to lie down for a bit now!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources