Aggregate yearly data based on different months using SQL

I have a table ‘Amounts’ where I have the monthly payments of customers. Every customer has a row for each payment he has made. I want to aggregate their payments yearly starting from the month they paid first. For example in the table given below, for userID 132, I want to aggregate his payments starting from month 9 of 2019 to month 8 of 2020 (one full year) as one row and then again from month 9 of 2020 to the next as another row.

Basically I want the yearly amounts of users as rows based on the month they joined. I’m not sure of how to aggregate this data using SQL and would appreciate help here.

Sample table (if it’s simpler, I can combine the year and month column as a date column in the raw data itself) >

+--------+------+-------+--------+
| userID | year | month | amount |
+--------+------+-------+--------+
| 132    | 2019 | 9     | 836    |
+--------+------+-------+--------+
| 132    | 2019 | 10    | 702    |
+--------+------+-------+--------+
| 132    | 2019 | 11    | 161    |
+--------+------+-------+--------+
| 132    | 2019 | 12    | 955    |
+--------+------+-------+--------+
| 132    | 2020 | 1     | 969    |
+--------+------+-------+--------+
| 132    | 2020 | 2     | 977    |
+--------+------+-------+--------+
| 132    | 2020 | 3     | 986    |
+--------+------+-------+--------+
| 132    | 2020 | 4     | 639    |
+--------+------+-------+--------+
| 132    | 2020 | 5     | 411    |
+--------+------+-------+--------+
| 132    | 2020 | 6     | 302    |
+--------+------+-------+--------+
| 132    | 2020 | 7     | 929    |
+--------+------+-------+--------+
| 132    | 2020 | 8     | 884    |
+--------+------+-------+--------+
| 132    | 2020 | 9     | 644    |
+--------+------+-------+--------+
| 132    | 2020 | 10    | 640    |
+--------+------+-------+--------+
| 132    | 2020 | 11    | 121    |
+--------+------+-------+--------+
| 132    | 2020 | 12    | 980    |
+--------+------+-------+--------+
| 1447   | 2020 | 11    | 356    |
+--------+------+-------+--------+
| 1447   | 2020 | 12    | 351    |
+--------+------+-------+--------+

Sample Output (the year cycle column here is just to indicate which year the total belongs since the users joined).>

+--------+------------+----------------------+
| userID | Year Cycle | Current Total Amount |
+--------+------------+----------------------+
| 132    | 1          | 8751                 |
+--------+------------+----------------------+
| 132    | 2          | 2385                 |
+--------+------------+----------------------+
| 1447   | 1          | 707                  |
+--------+------------+----------------------+

Answer

You can use the row_number() to generate a sequence a number for each user and then group every 12 as 1 cycle

select userId, cycle, sum(amount)
from
(
    select *, 
           cycle = (row_number() over (partition by userId 
                                           order by year, month) - 1) / 12 + 1
    from   Amounts
) t
group by userId, cycle

db<>fiddle demo

Advertisement

Answer