Skip to content
Advertisement

Sql stored procedure Count equity turnover for brooker

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;
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement