DB2 – Select rows with oldest date per id and aggregate a field with similar date

aggregatedb2

Here's what I want to do (Please see photo for reference):

I wanted to select from Source Table all customer (cust_id) and their corresponding oldest date of payment (pd_date) and aggregate using sum() function all similar oldest dates in result set per customer.

Please see highlighted in green, as case 1, for customer with oldest dates = 6/1/2020 but two payments (aggregated in result set) and case 2, in orange having no similar oldest dates thus the result.

select rows with earliest date per customer and aggregate paid amount with all similar dates

Best Answer

It's always good to provide sample data as insert statements along with create table statements. Now, the following is completely untested:

SELECT cust_id, SUM(paid_amt) as paid_amt, pd_date
FROM (
    SELECT cust_id, paid_amt, pd_date
         , RANK() OVER (PARTITION BY cust_id
                        ORDER BY pd_date desc) AS rnk
    FROM T
)
WHERE rnk = 1
GROUP BY cust_id, pd_date