CODEWITHSUNDEEP
sqlsql-serversumaggregate
AOB
AOB

Reputation: 49

Group/Aggregate on different values of a column in SQL

DB is SQL Server 2008

Table A:

Values in 'Identifier' Column: A, B, C, D, E, F Values in 'Balances' Column: 10, 100, 30, 50, 70, 55

Desired Result Set:

Values in 'Identifier_New' Column: Z, Y, X Values in sum(Balances) Column: 40, 170, 105

Logical map to sum balances:

sum and group balances of Identifiers A and C as Z. sum and group balances of Identifiers B and E as Y. sum and group balances of Identifiers D and E as X.

Can I get the above desired result set in a single sql?

Thanks

Upvotes: 0

Views: 318

Answers (2)

Stan Shaw
Stan Shaw

Reputation: 3034

SELECT CASE WHEN Identifier = 'A' or Identifier = 'C' THEN 'Z'
    WHEN Identifier = 'B' or Identifier = 'E' THEN 'Y'
    WHEN Identifier = 'D' or Identifier = 'F' THEN 'X' 
    END AS [Identifier_New],
    SUM(Balances) AS [Sum of Balances]
FROM TABLE A
GROUP BY CASE WHEN Identifier = 'A' or Identifier = 'C' THEN 'Z'
    WHEN Identifier = 'B' or Identifier = 'E' THEN 'Y'
    WHEN Identifier = 'D' or Identifier = 'F' THEN 'X' 
    END

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72235

Try this:

SELECT x.v AS Identifier_New, SUM(Balances)
FROM mytable
CROSS APPLY (SELECT CASE Identifier
                      WHEN 'A' THEN 'Z'
                      WHEN 'C' THEN 'Z'
                      WHEN 'B' THEN 'Y'
                      WHEN 'E' THEN 'Y'
                      WHEN 'D' THEN 'X'
                      WHEN 'F' THEN 'X'
                    END ) AS x(v)
GROUP BY x.v

Demo here

Upvotes: 3

Related Questions