Sum of value taking distinct ID and latest date-CodePudding

My table goes like

ID       Date        Value

ID1     01.01.2022    10

ID1     01.02.2022    10

ID1     01.03.2022    20

ID1     01.04.2022    30

ID2     01.01.2022    20

Expected result - 50 (Value 30 for ID1 with date 1.04.2022 Value 20 from ID2)

I know I can get such table (with only ID1 last date and ID2 last date) using:

SELECT ID, VALUE, DATE FROM ( 
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE ASC)
 AS ROWNUM 
FROM "Table") X 
WHERE ROWNUM = 1

but dont now how to SUM (Value) in this. Could you help me?

CodePudding user response：

You are on the right track. You should use ROW_NUMBER in a subquery to identify the latest row for each ID value. Then sum the value over that intermediate table.

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date DESC) rn
    FROM yourTable
)

SELECT SUM(Value) AS Total
FROM cte
WHERE rn = 1;