Question

# Conditional Sum not Working

• 2 replies
• 290 views

• Member
• 4 replies

I’m calculating a conditional sum that looks like this:

type: sum

sql:

CASE WHEN \${typ} = 0

THEN \${x} * -1

WHEN \${typ} = 1

THEN \${x} * -1

WHEN \${typ} = 2

THEN (\${A} + \${B} + \${C} + \${D}) * -1

WHEN \${typ} = 6

THEN \${y} * -1

WHEN \${typ} = 7

THEN \${y} * -1

END ;;

I’m focused on typ [type] 2. When I compute this sum for a particular row, A B C are all null, and D has a value. The output of this is 0 and I don’t understand why. If I change the formula to just D, then D will populate, however when I compute (A + B + C + D) * -1, the output is 0. I tried wrapping A B C in nullif statements but the result is still 0. A B C aren’t even null, they are 0 in the data so that shouldn’t matter.

Why would this be? Why is D being ignored and the sum is just 0?

Thank you!

### 2 replies

• jesus.cruz • Looker Staff
• 27 replies

@jimRL As written above, there may be a NULL value in the SUM(A+B+C+D) that is causing the erroneous output. Using COALESCE() around the SUM may help resolve any issues with NULLs in the sum. For example, you could do the following:

COALESCE(SUM(A+B+C+D), 0)

Or

COALESCE(SUM(A+B+C+D), D)

With that you will either use the summed value or, if the sum has a NULL, then your output could be 0 or the D value, respectively.

Userlevel 6 • fabio • Looker Staff
• 167 replies

If any of A, B, or C are null, the value being summed would be null, since `NULL + 1` evaluates to `NULL`. Additionally, Looker’s `type:sum` coerces NULL sums to 0 (after summing, not before). You mentioned you tried using nullif, but I think you actually want the opposite, a coalesce nulls to 0. So:

``````type: sum
sql:
CASE WHEN \${typ} = 2
THEN (COALESCE(\${A},0) + COALESCE(\${B},0) + COALESCE(\${C},0) + COALESCE(\${D},0)) * -1
END ;;
``````