CODEWITHSUNDEEP
sqlsasaggregate-functions
user4970373
user4970373

Reputation: 71

SAS/SQL aggregate by multiple columns

I have some data that looks like this

Trade ID    Trade Party 1   Trade Party 2    Trade 
    1       Trader 1        Trader 2         100000.00 
    2       Trader 2        Trader 1         50000.00 
    3       Trader 2        Trader 3         10000.00 
    4       Trader 1        Trader 3         15000.00 
    5       Trader 4        Trader 5         17000.00

I need to transform it to:

Trader  Total
Trader 1     165000.00 
Trader 2     60000.00 
Trader 3     45000.00 
Trader 4     17000.00 
Trader 5     17000.00

That is for each trader (regardless of whether they appear as Trader 1 or Trader 2) I need to sum their trades.

Upvotes: 2

Views: 112

Answers (1)

potashin
potashin

Reputation: 44581

You can try with union all:

select Trader
     , sum(Trade) as Total
from (
    select Trade_Party_1 as Trader, Trade from tbl
    union all
    select Trade_Party_2 as Trader, Trade from tbl ) t
group by Trader

Upvotes: 2

Related Questions