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.
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:
100 * (Sum([Amount1]) - Sum([Amount2])) / NullIf(Sum([Amount2]), 0) AS [ABWEICHUNG],