CODEWITHSUNDEEP
sqlsql-server
sabre
sabre

Reputation: 197

Selecting most recent record for each group with a sum function

This is a sample table

ID | Serial | Quantity | Date_Created
-------------------------------------
 1 | AS1GD  |    10    | 2014-12-25 8:00:00 AM
 1 | GO9A4  |     5    | 2014-12-28 9:04:32 AM
 2 | JF8WS  |    15    | 2014-12-29 9:23:43 AM
 2 | JFLE0  |    15    | 2015-01-04 10:53:12 AM
 2 | S8A4A  |    10    | 2015-01-05 9:12:46 AM
 3 | FXOE3  |    20    | 2015-01-03 9:31:52 AM
 3 | LSOR9  |    22    | 2015-01-06 12:00:44 PM

My expected result 

ID | Serial | Total_Quantity | Last_DateCreated
-------------------------------------------------
 1 | GO9A4  |     15         | 2014-12-28 9:04:32 AM
 2 | S8A4A  |     40         | 2015-01-05 9:12:46 AM
 3 | LSOR9  |     42         | 2015-01-06 12:00:44 PM

Here's a query I tried but it's not returning the sum but only the quantity of the record

WITH total AS
( SELECT [ID], [date_created], [serial], sum(quantity) as qty,  
 ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [date_created] DESC) AS rownum
 FROM [table]
group by ID, date_created, serial
)
SELECT ID, Serial, qty, date_created
FROM total
WHERE rownum = 1

Upvotes: 0

Views: 580

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415880

This will work as long as you don't have two records with the same ID created in the same second:

WITH RecentSUM AS
(
   SELECT ID, MAX(DateCreated) DateCreated, SUM(Quantity) TotalQuantity
   FROM [table]
   GROUP BY ID
)
SELECT t.ID, t.Serial, r.TotalQuantity, r.DateCreated
FROM RecentSUM r
INNER JOIN [table] t ON t.ID = r.ID and t.DateCreated=r.DateCreated;

Upvotes: 1

Hart CO
Hart CO

Reputation: 34774

Since you are grouping by more than the ID but want the SUM() at the ID level, you can add OVER() to your SUM():

;WITH total AS ( SELECT [ID]
                      , [date_created]
                      , [serial]
                      , SUM(SUM(quantity)) OVER(PARTITION BY [ID]) as qty
                      , ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [date_created] DESC) AS rownum
                FROM [table]
                GROUP BY ID, date_created, serial
                )
SELECT ID, Serial, qty, date_created
FROM total
WHERE rownum = 1

The above creates an oddity in which you need two SUM() in order to use the OVER(), but you can ditch the GROUP BY altogether in your example:

;WITH total AS ( SELECT [ID]
                      , [date_created]
                      , [serial]
                      , SUM(quantity) OVER(PARTITION BY [ID]) as qty
                      , ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [date_created] DESC) AS rownum
                FROM Table1
                )
SELECT ID, Serial, qty, date_created
FROM total
WHERE rownum = 1

Demo: SQL Fiddle

Upvotes: 4

Related Questions