CODEWITHSUNDEEP
sqlms-access
user2296381
user2296381

Reputation: 197

Group By in conjunction with combining groups?

Looking for some help on how to Group By in conjunction with combining the grouped by items. Any chance someone would be able to help here?

Example below: Multiple transactions in the same state with transaction amounts. I want to group transactions A+B together, but keep C transactions separate.

Data:

Transaction Type, State, Amount
A, SC, 43.00
B, SC, 44.00
C, SC, 45.00
B, SC, 46.00

What I want the output to look like is:

A+B, SC, 133.00
C, SC, 45.00

Upvotes: 1

Views: 71

Answers (1)

D Stanley
D Stanley

Reputation: 152511

How about

SELECT 
CASE 
    WHEN [Transaction Type] IN ('A','B') 
        THEN 'A+B'
    ELSE [Transaction Type] 
    END [Transaction Type],
State,
SUM(Amount) Total
FROM MyTable
GROUP BY 
    CASE 
        WHEN [Transaction Type] IN ('A','B') 
            THEN 'A+B'
        ELSE [Transaction Type] 
    END, 
    State

?

Upvotes: 1

Related Questions