sql - 获取由另一列分组然后分组到另一列的列的总和

Question

我已经尝试解决这个问题有一段时间了，但我缺乏更高级的 SQL 技能，这阻碍了我。

Executions(TradeDate, Symbol, Side, Price, Under, Account)

TEMP DATA:
2012-06-20, AAPL 120716C00600000, BUY, 3.25, AAPL, XYZ123
2012-06-20, AAPL 120716C00600000, SELL, 3.30, AAPL, XYZ123
2012-06-20, AAPL 120716C00600000, BUY, 3.25, AAPL, XYZ123
2012-06-20, AAPL 120716C00600000, SELL, 3.30, AAPL, XYZ123
2012-06-20, GRPN 120716C00027000, BUY, 2.25, GRPN, XYZ123
2012-06-20, GRPN 120716C00027000, SELL, 2.30, GRPN, XYZ123
2012-06-20, GRPN 120716C00027000, SELL, 2.30, GRPN, XYZ123
2012-06-20, GRPN 120716C00027000, BUY, 2.25, GRPN, XYZ123


-UNDER----Side(Buy)----Side(Sell)
 AAPL      6.50         6.60
 GRPN      4.50         4.60

如您所见，我正在尝试获取每一方的价格总和，然后按下方分组。

Answer 1

使用GROUP BY按Under列和与SUM关联的CASE进行分组，以获得所需的结果:

SELECT e.Under,
       SUM(case when e.Side = 'BUY' them e.Price else 0 end) as 'Side(Buy)',
       SUM(case when e.Side = 'SELL' them e.Price else 0 end) as 'Side(Sell)'
FROM Executions e
GROUP BY e.Under