I have a table with monthly amounts per ID, where in some of these months an invoice takes place.
My table looks like:
| ID | Date | Invoiced | Amount |
|---|---|---|---|
| AAA | 2023-01 | 0 | 10 |
| AAA | 2023-02 | 0 | 15 |
| AAA | 2023-03 | 1 | 15 |
| AAA | 2023-04 | 0 | 10 |
| AAA | 2023-05 | 0 | 10 |
| AAA | 2023-06 | 1 | 10 |
| BBB | 2022-05 | 0 | 40 |
| BBB | 2022-06 | 1 | 20 |
| BBB | 2022-07 | 0 | 30 |
| BBB | 2022-08 | 1 | 30 |
I need to have the rows only per ID with the invoice months, where we sum over the previous amounts since the last invoice. What I want to have:
| ID | Date | Invoiced | Amount |
|---|---|---|---|
| AAA | 2023-03 | 1 | 40 |
| AAA | 2023-06 | 1 | 30 |
| BBB | 2022-06 | 1 | 60 |
| BBB | 2022-08 | 1 | 60 |
How this can be done in postgresql? I started with the query below, but it gives not yet what I need.
SELECT "ID", "Date", "Invoiced"
, sum(Amount) OVER (PARTITION BY "Invoiced" ORDER BY "Id", "Date") AS Amount
FROM Table
You have a gaps and islands problem, you could use SUM() as window function in descending order, to give a unique id to each successive group of rows (0 values then 1) :
WITH cte AS (
SELECT *, sum(invoiced) OVER (PARTITION BY ID ORDER BY Date desc) grp
FROM mytable
ORDER BY ID, Date
)
SELECT ID, MAX(date) AS Date, MAX(Invoiced) AS Invoiced, SUM(Amount) AS Amount
FROM cte
GROUP BY ID, grp
ORDER BY ID, Date