I have a table like this:
+-----------+------------+---------------+-------------+--------------+
| member_id | balance | amount | new_balance | time |
+-----------+------------+---------------+-------------+--------------+
| 5 | 630 | -30 | 600 | 2017-08-14 |
| 3 | 142 | -68 | 74 | 2017-08-14 |
| 3 | 120 | 22 | 142 | 2017-08-13 |
| 3 | 0 | 120 | 120 | 2017-08-12 |
| 20 | 300 | 324 | 624 | 2017-08-12 |
| 4 | 100 | -30 | 70 | 2017-08-11 |
| 4 | 0 | 100 | 100 | 2017-08-10 |
| 5 | 30 | 600 | 630 | 2017-08-10 |
+-----------+------------+---------------+-------------+--------------+
And what I expect to select is something like:
+-----------+------------+---------------+---------------+-------------+
| member_id | balance | in_amount | out_amount | new_balance |
+-----------+------------+---------------+---------------+-------------+
| 3 | 0 | 142 | 0 | 142 |
| 4 | 100 | 0 | -30 | 70 |
| 5 | 630 | 0 | 0 | 630 |
| 20 | 300 | 324 | 0 | 624 |
+-----------+------------+---------------+---------------+-------------+
Which is the summary within a certain period (2017-08-11 to 2017-08-13).
balance: the balance during the start of the period;
in_amount: the sum of positive amount within the period;
out_amount: the sum of negative amount within the period;
new_balance: the balance at the end of the period;
I have work out a statement like, in order to obtain the income and outcome:
set @start = "2017-08-11 00:00:00";
set @end = "2017-08-13 00:00:00";
SELECT
DISTINCT(member_id),
sum(if(amount>0, amount, 0)) as in_amount,
sum(if(amount<0, amount, 0)) as out_amount,
FROM transaction
WHERE
(time BETWEEN @start and @end)
group by member_id;
However, I have no idea to include the balance and new_balance into the statement. Does someone have any suggestion to me??