CODEWITHSUNDEEP
sqlsql-server
Rakshit
Rakshit

Reputation: 85

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                  |
+--------+------------+----------------------+

Upvotes: 2

Views: 1016

Answers (2)

Karan
Karan

Reputation: 12619

References -

  1. Common Table Expressions (CTE), https://www.sqlshack.com/sql-server-common-table-expressions-cte/
  2. SELECT - OVER Clause

Use CTE like below to get your desired outcome.

Get date column to get minimum date for each user with first CTE AmountWithDate.

Then create another CTE AmountWithYearDifference using previous CTE AmountWithDate to get years from user's first transaction. Have to use over(partition by ...)

Then use AmountWithYearDifference with GROUP BY with userid & dt and SUM(Amount) to get desired output.

;WITH AmountWithDate AS (
    SELECT *, DATEFROMPARTS(year, month, 1) AS dt 
    FROM Amounts
)
, AmountWithYearDifference AS (
    SELECT *, DATEDIFF(MONTH, (min(dt) over(partition by userid)), dt) / 12 AS years
    FROM AmountWithDate
)
SELECT userid, 
        min(dt) AS dt, DATEPART(MONTH, MIN(dt)) as month, 
        DATEPART(YEAR, MIN(dt)) as year, 
        sum(Amount)
FROM AmountWithYearDifference
GROUP BY userid, years

Edit if you already have date column then no need to have first CTE and you can go with directly second CTE. I assume that your date column name is dt.

;WITH AmountWithYearDifference AS (
    SELECT *, DATEDIFF(MONTH, (min(dt) over(partition by userid)), dt) / 12 AS years
    FROM Amounts
)
SELECT userid, 
        min(dt) AS dt, DATEPART(MONTH, MIN(dt)) as month, 
        DATEPART(YEAR, MIN(dt)) as year, 
        sum(Amount)
FROM AmountWithYearDifference
GROUP BY userid, years

If you are not familiar with CTE and wish to go with inner sql query then you can write query like below. Just write query from CTE in inner query as below.

SELECT userid, 
        min(dt) AS dt, DATEPART(MONTH, MIN(dt)) as month, 
        DATEPART(YEAR, MIN(dt)) as year, 
        sum(Amount)
FROM (
    SELECT *, DATEDIFF(MONTH, (min(dt) over(partition by userid)), dt) / 12 AS years
    FROM Amounts
)
GROUP BY userid, years

Upvotes: 2

Squirrel
Squirrel

Reputation: 24763

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

Upvotes: 1

Related Questions