SQL Server Value divided by zero

Question

0.00/5 (No votes)

See more:

SQL

SELECT Sum(DailySum)as DailySum, sum(Unified) as Unified,
(Sum(DailySum)/sum(Unified)) AS HPPL
FROM ProductionEntry

Here in database the value of Unified is 0. As a result when Unified is trying to divide DailySum then it arises error. Both of the field's datatype are decimal. Please help me.

Posted 23-Feb-14 23:50pm

Sumon562

Add a Solution

4 solutions

Solution 4

There is few ways at least to avoid 'divide by zero' error.
1) NULLIF[^] function - zero is replaced with NULL. Any operation on NULL returns NULL always ;)

SQL

SELECT Field1, Field2, Field1/NULLIF(Field2,0) AS Result
FROM TableName

2) CASE ... END[^] statement - returns divide result only for non-zero values

SQL

SELECT Field1, Field2, CASE WHEN Field2 = 0 THEN 0 ELSE Field1/Field2 END AS Result
FROM TableName

3) WHERE[^] clause - returns non-zero values in subquery; note: Field2 data equal to zero are ignored!

SQL

SELECT Field1, Field2, Field1/Field2 AS Result
FROM (
    SELECT Field1, Field2
    FROM TableName
    WHERE Field2>0
) AS A

Posted 24-Feb-14 0:18am

Maciej Los

Updated 24-Feb-14 0:26am

v3

Comments

Abhinav S 24-Feb-14 6:35am

5!

Maciej Los 24-Feb-14 6:44am

Thank you ;)

Solution 2

Change your query slightly by putting a case statement.

SQL

SELECT Sum(DailySum)as DailySum, sum(Unified) as Unified,
case when sum(Unified) != 0 (Sum(DailySum)/sum(Unified)) else 'NA' end AS HPPL
FROM ProductionEntry

NA is not applicable for cases when sum of unified is 0.

Posted 24-Feb-14 0:05am

Abhinav S

Comments

Maciej Los 24-Feb-14 6:27am

In my opinion - partial answer, +4!
Please see my solution.

Abhinav S 24-Feb-14 6:35am

Thank you.

Solution 3

a better way would be to do (SUM(DailySum)/ NULLIF(SUM(Unified), 0))

Posted 24-Feb-14 0:07am

db7uk

Comments

Maciej Los 24-Feb-14 6:27am

In my opinion - partial answer, +4!
Please see my solution.

db7uk 24-Feb-14 6:51am

Yep completely agree. The solution you provided was better in terms of coverage.

Maciej Los 24-Feb-14 7:08am

Thank you ;)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tejas Vaishnav · Accepted Answer · 2014-02-24T00:02:00

as you mention that, Unified is 0 and you are trying to use it with divide operation then it will giving you error because we can not divide any value with 0. in the same way if you divide any value with 1 then you got answer as the same value, so in your case you can use this type of query...

SQL

SELECT
    SUM(DailySum)as DailySum
    ,SUM(Unified) as Unified
    ,(SUM(DailySum)/
        CASE WHEN SUM(Unified) > 0 THEN SUM(Unified) ELSE 1 END
    ) AS HPPL
FROM ProductionEntry

I have checked that, if Sum of Unified is 0 then it will replace with 1 or if it is more then 0 then it will use actual value

SQL Server Value divided by zero

4 solutions

Solution 4

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us