x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 160

# Case statement to sum values - T-SQL

I'm trying to write a case statement  that sums up values, but the one below is producing null values. What am I doing wrong? *In a nutshell, (NL * 3%) + (DP * 1%) + (PR) = NR.

sum((case
when METRIC_CD = ''NL'' then
MTD_ACT * 0.03 else null end) +
(case when METRIC_CD = ''DP'' then
MTD_ACT * 0.01 else null end) +
(case when METRIC_CD = ''PR'' then
MTD_ACT * 1 else null end)) as NR

Below is the data  as it appears in the table:

METRIC_CD      MTD_ACT
NL                       1200
DP                         500
PR                      3150
DP                      2250
PR                        550
NL                    11000

Thank you for your tremendous assistance.
0
saved4use
2 Solutions

Commented:
Instead of else null use else 0 (that's zero).

Adding 0 won't affect your total but adding NULL will yield a NULL result. Take the following examples:

SELECT 1 + 3 + 0 + 5 => returns 9

SELECT 1 + 3 + NULL + 5 => returns NULL

The same holds true for the SUM() function. A NULL value in the aggregate will yield NULL.
0

Microsoft SQL Server Developer, Architect, and AuthorCommented:
Russ is correct, as NULL added to anything else will equal NULL.  And to make this a hell of a lot easier to read...
SUM(CASE METRIC_CD
WHEN 'NL' THEN MTD_ACT * 0.03
WHEN 'DP' THEN MTD_ACT * 0.01
WHEN 'PR' THEN MTD_ACT * 1 ELSE 0 END) as NR
0

Commented:
Try this

sum((case
when METRIC_CD = ''NL'' then MTD_ACT * 0.03
when METRIC_CD = ''DP'' then MTD_ACT * 0.01
when METRIC_CD = ''PR'' then MTD_ACT * 1
else 0 end)) as NR
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.