Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
There is the following table:
[payments]:
Load * Inline
[
ID, USER_ID, AMOUNT, CREATED_AT
1, 1, 20, 2023-01-30 06:38:36
2, 2, 15, 2023-01-30 07:17:17
3, 1, 25, 2023-01-31 08:18:18
4, 3, 10, 2023-01-31 09:19:19
5, 3, 20, 2023-01-31 10:10:10
6, 1, 15, 2023-01-31 11:11:11
7, 1, 20, 2023-02-25 06:38:36
8, 2, 15, 2023-02-25 07:17:17
9, 1, 25, 2023-02-26 08:18:18
10, 1, 15, 2023-02-27 11:11:11
11, 2, 15, 2023-03-28 07:17:17
12, 3, 10, 2023-03-29 09:19:19
13, 3, 20, 2023-03-31 10:10:10
14, 3, 25, 2023-03-31 11:11:11
];
and it looks like this:
We aim to create a table where each MonthYear (e.g., Mar 2022) corresponds to one record (row) for every user who made payments during that MonthYear. Each record (row) will include the following columns:
For example, if user_id=3 made 100 payments in Mar 2022, and the amount of each payment was 2, then that table will contain one record for that user for Mar 2022:
Mar 2022 | 3 | 200 | 100 |
For the "payments" table listed above, the output would look like this:
MonthYear | user_id | sum_amounts | count_payments |
Jan 2023 | 1 | 60 | 3 |
Jan 2023 | 2 | 15 | 1 |
Jan 2023 | 3 | 30 | 2 |
Feb 2023 | 1 | 60 | 3 |
Feb 2023 | 2 | 15 | 1 |
Mar 2023 | 2 | 15 | 1 |
Mar 2023 | 3 | 55 | 3 |
Do you have any suggestions on how we can achieve this? 🤔
Hello there,
I was able to do that through creating a 'Resident' table that derives from your original "payments" table:
Hello there,
I was able to do that through creating a 'Resident' table that derives from your original "payments" table:
Thank you @Ryan_McGregor !