SAS/SQL aggregate by multiple columns

Question

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.

potashin · Accepted Answer · 2015-06-26T19:36:59.193

2

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

edited Jun 26 '15 at 19:36

answered Jun 26 '15 at 19:34

potashin

44,205
11
83
107

SAS/SQL aggregate by multiple columns

1 Answers1