CODEWITHSUNDEEP
mysql
Jacky Lau
Jacky Lau

Reputation: 723

MYSQL How Can I Select the sum column value and specific row entry in one statement?

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

Upvotes: 3

Views: 130

Answers (2)

Guillaume Georges
Guillaume Georges

Reputation: 4020

Using two sub queries along with a LEFT JOIN.

The first sub query retrieves the most recent balance. The second sub query sums up in and out amount for each member, using the date parameters.

Using a LEFT JOIN allows us to get the balance for each member, including those who didn't make transaction between @start and @end

set @start = "2017-08-11 00:00:00";
set @end = "2017-08-13 00:00:00";
select
    lastTransaction.member_id, 
    lastTransaction.balance,
    IFNULL(computeInOut.in_amount, 0) as in_amount, 
    IFNULL(computeInOut.out_amount, 0) as out_amount, 
    lastTransaction.balance, 
    -- balance is the most recent balance + computed in amout - computed out amount
    (lastTransaction.balance + IFNULL(computeInOut.in_amount, 0) + IFNULL(computeInOut.out_amount, 0)) as new_balance
FROM 
    ( -- retrieving the most recent balance information prior to @start
    SELECT 
        member_id,
        max(time) as mostRecentTransaction, 
        balance
    FROM transaction 
    WHERE time <= @start
    group by member_id
     ) as lastTransaction
LEFT JOIN 
    ( -- calculating in & out amounts sum based on the date parameters
    SELECT 
        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
    ) as computeInOut
ON (lastTransaction.member_id = computeInOut.member_id)

Result : 

+-----------+---------+--------+---------+------------+
| member_id | balance | inflow | outflow | newbalance |
+-----------+---------+--------+---------+------------+
|         4 |     100 |      0 |     -30 |         70 |
|         5 |       30|      0 |       0 |         30 |
+-----------+---------+--------+---------+------------+

Members 5 and 20 are not in the result because their first transaction occured after @start, so there is no balance prior to the events (transactions between the 11h and the 13th)

Upvotes: 1

P.Salmon
P.Salmon

Reputation: 17615

The balance field can be either at the start of the period or in the first date in the period so a sub query with limit should find these and a coalesce can decide which to use. The new balance has to be the last date in the period so a sub query with a desc order by and limit should find these. 

set @start = "2017-08-11 00:00:00";
set @end = "2017-08-13 00:00:00";
select member_id, 
        coalesce(
         (select t1.balance from t t1 where t1.member_id = t.member_id and t1.dt  = @start order by t1.dt desc limit 1)
         ,(select t1.balance from t t1 where t1.member_id = t.member_id and t1.dt > @start order by t1.dt asc limit 1)
         ) balance, 
         sum(case when amount > 0 then amount else 0 end) inflow,
         sum(case when amount < 0 then amount else 0 end) outflow,
         (select t1.new_balance from t t1 where t1.member_id = t.member_id and t1.dt <= @end order by t.dt desc limit 1) newbalance
from t
where t.dt between @start and @end
group by member_id

Result

+-----------+---------+--------+---------+------------+
| member_id | balance | inflow | outflow | newbalance |
+-----------+---------+--------+---------+------------+
|         3 |       0 |    142 |       0 |        142 |
|         4 |     100 |      0 |     -30 |         70 |
|        20 |     300 |    324 |       0 |        624 |
+-----------+---------+--------+---------+------------+
3 rows in set (0.00 sec)

And 5 drops out because there are no events in the period.

Upvotes: 1

Related Questions