Last SQL record for each group with aggregated column

Question

I have a table like 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 the latest price (where the latest is only max trade_time) and the aggregated volume, so in the above table I want to see:

123  6 250  
456  8 450 

I just found that the current request is not working (always), i.e. There is no guarantee that the selected price is always the latest:

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

Can I do without subqueries? Thank!

Answer 1

Since you did not specify the database you are using, here is some kind of 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

SQL Server 2005 CTE (Common Table Expression), , :

;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

, , , , ....

Answer 3

  select a.stock_id, b.price , sum(a.volume) from tablename a
       join (select stock_id, max(trade_time), price from tablename
             group by stock_id) b 
             on a.stock_id = b.stock_id
    group by stock_id

Answer 4

@Stock (STOCK_ID int, TRADE_TIME int, PRICE int, VOLUME int)

@Stock (123,1,5,100), (123,2,6,150), (456,1,7,200), (456,2,8,250)

Stock_ID, , ( () @Stock B, B.Stock_ID = A.Stock_ID) @Stock A A.Trade_Time = ( max (Trade_Time) @Stock)