How to avoid Error 8134 level 16

Question

0.00/5 (No votes)

See more:

Hi I have this Tsql statement but I'm getting the above (question) error.

I wanna get the Percentage differance between Amount1 and Amount2

I learnt i have to use a CASE Statement but i ain't getting it right.

My statement:

SQL

SELECT 
[Projects],
Sum([Amount1]) AS [Total_Amount1], 
Sum([Amount2]) AS [Total_Amount2],,
Sum([Amount1]) - Sum([Amount2]) AS [DELTA],

Sum([Amount1]) - Sum([Amount2])/ Sum([Amount2]) * 100 AS [ABWEICHUNG],

Sum([Amount3]) AS [Total_Amount3],
Sum([Amount4]) AS [Total_Amount4]
FROM tbl1
GROUP BY [Projects]

Thanks

Posted 24-Feb-15 3:16am

mikybrain1

Updated 24-Feb-15 3:41am

v4

Add a Solution

3 solutions

Solution 3

Error 8134 is "divide by zero" - it would have been helpful to include that information in the question!

The error occurs because Sum(Amount2) is returning 0 for at least one group. You can avoid the error by converting 0 to NULL using the NullIf function.

You'll also need parentheses around the Sum(Amount1) - Sum(Amount2), since the division operator has higher precedence than the subtraction operator.

SQL

Sum(Amount1) - Sum(Amount2) / Sum(Amount2)
=== Sum(Amount1) - (Sum(Amount2) / Sum(Amount2))
=== Sum(Amount1) - 1

Although technically correct, putting the * 100 at the end makes the calculation more confusing to read. It's not immediately obvious whether you're multiplying the numerator or the denominator by 100. Moving the multiplication to the start would make this clearer.

Try using:

SQL

100 * (Sum([Amount1]) - Sum([Amount2])) / NullIf(Sum([Amount2]), 0) AS [ABWEICHUNG],

Posted 24-Feb-15 4:12am

Richard Deeming

Solution 1

This line throws error

SQL

Sum([Amount1]) = 0 - Sum([Amount2])/ Sum([Amount2]) * 100 AS [ABWEICHUNG],

It should be..

SQL

(0 - Sum([Amount2])/ Sum([Amount2]) * 100) AS [ABWEICHUNG],

Please elaborate your question..what result exactly you need

Posted 24-Feb-15 3:23am

manak chand

Comments

mikybrain1 24-Feb-15 9:31am

That was a tip mistake from me. Question has been edited. My Fault. The error still exist

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)

ZurdoDev · Accepted Answer · 2015-02-24T04:05:00

Solution 2

8134 is a divide by zero. You can add a case statement to check when SUM(Amount2) is 0 and handle it differently or what I have done in the past is instead something like this:

SQL

Sum([Amount2])/ (Sum([Amount2]+0.0000001))

This will always avoid the divide by zero and adding that small amount likely won't affect any results, depending on your data.

Posted 24-Feb-15 4:05am

ZurdoDev

Comments

hypermellow 24-Feb-15 10:24am

5+ for the '+0.0000001' work around ... will be using this one myself. :)

mikybrain1 24-Feb-15 10:34am

@RyanDev. Its seems to be alright but I'm gonna cross check it with a calculator for the outcomes.

Thnx by the way

ZurdoDev 24-Feb-15 10:36am

You're welcome.