• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 153
  • Last Modified:

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
Asked:
saved4use
2 Solutions
 
Russ SuterCommented:
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
 
Jim HornMicrosoft 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

Open in new window

0
 
mbazarCommented:
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now