More SQL Server Aggregate functions

I mentioned that I would try to get back in here with some more examples using SQL Server's Aggregate functions. I think this is a good one. One of the requests I used to get again and again, was the current net position for all symbols traded on the Equity exchanges. The Ops desk (the Team supporting the traders) needed to know if positions were open for any symbol across these exchanges:    ISLD, BRUT, ARCA, CHX, NSDQ, NYSE.

To handle that, I just created this procedure which was run on the back-end, and would alert them if an open position was found.

  CREATE PROCEDURE dbo.usp_NetPosBySymbol
  AS
  SET NOCOUNT ON
  /*
   Returns the net position by symbol, across the equity exchanges, if exists.
   EXEC dbo.usp_NetPosBySymbol 

  Auth:  Me
  Date:  10/13/2014
  */
  BEGIN
        -- use this method to avoid an IN list in the WHERE clause
DECLARE @Exchanges TABLE (ExchangeName VARCHAR(15))
INSERT @Exchanges (ExchangeName)
VALUES 
('ARCA'),
('BRUT'),
('CHX'),
('DNET'),
('ISLD'),
('NSDQ'),
('NYSE');

IF EXISTS(
SELECT buys-sells AS Difference
FROM (
SELECT Symbol,SUM(Quantity) [buys] 
FROM dbo.Orders o
WHERE Side = 'B' 
         AND EXISTS(
                    SELECT 1 FROM @Exchanges e WHERE e.ExchangeName = o.Exchange)
GROUP BY Symbol) buys JOIN
(
SELECT Symbol,SUM(Quantity) [sells]
FROM dbo.Orders o 
WHERE Side = 'S' 
AND EXISTS(
                     SELECT 1 FROM @Exchanges e WHERE e.ExchangeName = o.Exchange)
GROUP BY Symbol) sells
         ON buys.Symbol = sells.Symbol
WHERE 
  buys <> sells)

BEGIN
SELECT buys.Symbol,Buys,Sells,buys-sells AS Difference
FROM(
SELECT Symbol,SUM(Quantity) [buys] 
FROM dbo.Orders o
WHERE Side = 'B' 
           AND EXISTS(
                      SELECT 1 FROM @Exchanges e WHERE e.ExchangeName = o.Exchange)
GROUP BY Symbol) buys JOIN
(
SELECT Symbol,SUM(Quantity) [sells]
FROM dbo.Orders o 
WHERE Side = 'S' 
             AND EXISTS(
                       SELECT 1 FROM @Exchanges e WHERE e.ExchangeName = o.Exchange)
GROUP BY Symbol) sells
              ON buys.Symbol = sells.Symbol
WHERE 
                   buys <> sells
ORDER BY 
                   buys.Symbol
       END
    END

  SET NOCOUNT OFF
  GO

We're still using the Orders data that we wrote in this post, SQL Aggregate functions, and these are our results:

   Symbol   Buys  Sells  Difference
   BEAS      55    100     -45
   YHOO      50    105     -55

That is just a simple example using SUM.  We first do an existence check, and then we return the net position back to the front end, if exists.

See this for more information, and for a couple more examples using the SUM function:

     http://msdn.microsoft.com/en-us/library/ms187810.aspx