Create case and sum values based on condition

Question

I'm looking for a way to both rename values in a case statement, then group those values together as their sums. It seems that all explanations focus either on creating the condition,setting criteria, or summing. I envision that the query should look something like this:

SELECT 
X, 
Y,
CASE Z
WHEN '1' THEN 'A'
WHEN '2' THEN 'B'
WHEN '3' THEN 'C'
WHEN '4' THEN 'D'
END +
SUM(CASE WHEN Z='1' OR Z='2' THEN 'I' END) +
SUM(CASE WHEN Z='3' OR Z='4' THEN 'J' END) AS Letters
FROM FOO

I may be making this more difficult than necessary. Really all I need is for 1 and 2 from Z to group into I and 3 and 4 from Z to group into J such that I will be the sum of all cases of 1 and 2 and J will be the sum of all case of 3 and 4, ideally in the same column. Any help would be greatly appreciated.

As requested by the community, the input data would look something like this:

INSERT INTO FOO 
(Z, Values)
VALUES
(1,3),
(2,6),
(3,10),
(4,20),
(5,1),
(1,9),
(2,12),
(3,30)
(4,40)
(5,2);

The outcome should create create a new category that sums values under 1 and 2 together under I and values under 3 and 4 together under J. Thus:

Letters Values
I       30
J       100

Where 5 is an excluded condition.

Please provide some example input and output data to illustrate what you are trying to accomplish. — Dale K, Mar 11 '19 at 22:07
As requested by @DaleBurrell, please provide input and output data as it is unclear what you are trying to achieve. "Conditional SUM" examples can be found here: https://stackoverflow.com/questions/16517298/conditional-sum-in-group-by-query-mssql — Alex, Mar 11 '19 at 22:22
Thanks Alex, I could use this solution if I could find a way to include two conditions under a single sum. Something along the lines of `SUM(CASE WHEN Z='1' OR Z='2' THEN I ELSE CASE WHEN Z='3' OR Z='4' THEN J END) Letters` — Bar-Tzur, Mar 12 '19 at 13:12

score 1 · Accepted Answer · answered Mar 12 '19 at 13:21

1

SELECT CASE [Z]
            WHEN 1 THEN 'I'
            WHEN 2 THEN 'I'
            WHEN 3 THEN 'J'
            WHEN 4 THEN 'J'
       END [Letters]
       ,SUM([Values]) [Values]
FROM FOO
GROUP BY CASE [Z]
              WHEN 1 THEN 'I'
              WHEN 2 THEN 'I'
              WHEN 3 THEN 'J'
              WHEN 4 THEN 'J'
         END

Or slightly shorter

SELECT CASE
            WHEN [Z] IN (1,2) THEN 'I'
            WHEN [Z] IN (3,4) THEN 'J'
       END [Letters]
       ,SUM([Values]) [Values]
FROM FOO
GROUP BY CASE
             WHEN [Z] IN (1,2) THEN 'I'
             WHEN [Z] IN (3,4) THEN 'J'
         END

DBFiddle Demo

answered Mar 12 '19 at 13:21

Andy

3,132
4
36
68

You need to add a `WHERE [Z] IN (1,2,3,4)`. – forpas Mar 12 '19 at 13:33
Thanks Andy, I appreciate you taking the time to answer my question. I ran into some trouble because I was not complete enough in my question. I should have mentioned that I was using `ORDER BY` as well. Once I figured out the `CASE` statement needed to be included here as well, your solution worked without a hitch. Thanks Again. – Bar-Tzur Mar 12 '19 at 15:19

Create case and sum values based on condition

1 Answers1