Sum of value taking distinct ID and latest date

July 26, 2022

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?

>Solution :

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;