CODEWITHSUNDEEP
sqlgroup-byaggregate-functions
JamesK
JamesK

Reputation: 43

SQL latest record per group with an aggregated column

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!

Upvotes: 4

Views: 4299

Answers (4)

Syed Barkath
Syed Barkath

Reputation: 1

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

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

Select Stock_ID,Price,(Select sum(Volume) from @Stock B where B.Stock_ID=A.Stock_ID)Volume from @Stock A where A.Trade_Time=(Select max(Trade_Time) from @Stock)

Upvotes: 0

jbemt48
jbemt48

Reputation: 419

  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

Upvotes: -1

marc_s
marc_s

Reputation: 754598

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....

Upvotes: 3

John Hartsock
John Hartsock

Reputation: 86882

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

Upvotes: 5

Related Questions