CODEWITHSUNDEEP
mysqlsql
MattH
MattH

Reputation: 68

SQL query to return sum of column grouped by months including all prior dates

I am trying to return the balance on an account summarized by month, grouped by account and month. For example, given a transactions table that looks like this:

| date       | account_id | trans_amount |
| 2012-01-01 |          1 |        -2500 |
| 2012-01-01 |          2 |        -2500 |
| 2016-01-01 |          1 |         5000 |
| 2016-01-01 |          2 |         5000 |
| 2016-02-01 |          1 |         7500 |
| 2016-02-01 |          2 |         7500 |

I'd like the query to return: 

| month | year | account_id | balance |
| 01    | 2012 |          1 |   -2500 |
| 01    | 2012 |          2 |   -2500 |
| 01    | 2016 |          1 |    2500 |
| 01    | 2016 |          2 |    2500 |
| 02    | 2016 |          1 |   10000 |
| 02    | 2016 |          2 |   10000 |

I started with this: 

SELECT MONTH(date), YEAR(date), account_id, SUM(trans_amount)
FROM transactions
GROUP BY account_id, MONTH(date), YEAR(date);

but of course that gives the change during the grouped period, not the balance including transactions prior to the period.

Upvotes: 1

Views: 54

Answers (1)

Tim B
Tim B

Reputation: 41188

The simplest way would be a nested query. Something like this:

SELECT MONTH(date) as month, YEAR(date) as year, account_id, (select SUM(trans_amount) from transactions t2 where t1.date>=t2.date and t1.account_id = t2.account_id) as balance
FROM transactions t1
GROUP BY account_id, MONTH(date), YEAR(date);

You will most likely need to fix the syntax a bit. I'm also not completely sure how well the dates will match up with the grouping so you might need to construct a synthetic date for the inner select.

Upvotes: 1

Related Questions