SQL latest record per group with an aggregated column

Question

I have a table similar to this:

STOCK_ID TRADE_TIME   PRICE     VOLUME  
123         1           5         100  
123         2           6         150  
456         1           7         200  
456         2           8         250

For each stock I want to get latest price (where latest is just the max trade_time) and aggregated volume, so for the above table I want to see:

123  6 250  
456  8 450 

I've just discovered that the current query doesn't (always) work, ie there's no guarantee that the price selected is always the latest:

select stock_id, price, sum(volume) group by stock_id

Is this possible to do without subqueries? Thanks!

Answer 1

As you didn't specify the database you are using Here is some generic SQL that will do what you want.

SELECT
  b.stock_id,
  b.trade_time,
  b.price,
  a.sumVolume
FROM (SELECT
        stock_id, 
        max(trade_time) AS maxtime,
        sum(volume) as sumVolume
      FROM stocktable
      GROUP BY stock_id) a
INNER JOIN stocktable b
  ON b.stock_id = a.stock_id and b.trade_time = a.maxtime

Answer 2

In SQL Server 2005 and up, you could use a CTE (Common Table Expression) to get what you're looking for:

;WITH MaxStocks AS
(
    SELECT
        stock_id, price, tradetime, volume,
        ROW_NUMBER() OVER(PARTITION BY stock_ID ORDER BY TradeTime DESC) 'RowNo'
    FROM    
        @stocks
)
SELECT
    m.StockID, m.Price, 
    (SELECT SUM(VOLUME) 
     FROM maxStocks m2 
     WHERE m2.STock_ID = m.Stock_ID) AS 'TotalVolume'
FROM maxStocks m
WHERE rowno = 1

Since you want both the last trade as well as the volume of all trades for each stock, I don't see how you could do this totally without subqueries, however....