I’m trying to count a brookers qty turnover, total buy sum, total sell sum. The problem is when the buyer is also the seller in which case the brooker has turnover the equities once on the buy side and once on the sell side. So if a brooker sells 150 equities to itself the turnover for that trade should be 300.
Table:
| InstrumentId | Price | Qty | Buyer | Seller | Date ----------------------------------------------------- | 265 | 22 | 100 | A | B | ---- ------------------------------------------------------- | 265 | 23 | 150 | A | A | ---- ------------------------------------------------------- | 265 | 21 | 75 | C | A | ---- ---------------------------------------------------- Expected result: turnover 475. Buy total: 250. Sell total: 225.
Advertisement
Answer
One method is:
select broker, sum(qty) from ((select buyer as broker, qty from sales) union all (select seller, qty from sales) ) b group by broker;