MySQL multiple sums, using numbers from multiple tables

Question

Let's say I have two tables, one storing accounts, one storing transactions :

id   acct_name   opening_bal
1    checking    1029.99
2    savings     2002.19
...

And

id   date         amount   from_acct   to_acct
...
99   2018-01-21   12.15    1           2
100  2018-01-21   9.99     4           1
101  2018-01-23   10.01    5           2
...

For example, row 99 in the transactions table is saying that 12.15 was transfered from checking to savings on 21 Jan 2018.

I would like to write a query that returns all accounts together with their balances on a given date (like today or 12 Oct 2018, etc), something like this :

acct        balance
checking    1599.21
savings     2221.99
...

How would I write such a query?

Edit: Here's a solution, which is close enough to what I want (it just has an additional id column). You can replace CURDATE() with an arbitrary date to get the corresponding table of balances on that date.

SELECT id, acct_name, opening_bal+amt-amt2 as balance FROM accounts 
  INNER JOIN    
    (SELECT to_acct, sum(amount) AS amt 
     FROM transactions
     WHERE date <= CURDATE() 
     GROUP BY to_acct) as T2   
    ON accounts.id=T2.to_acct 
  INNER JOIN    
    (SELECT from_acct, sum(amount) AS amt2 
     FROM transactions 
     WHERE date <= CURDATE()
     GROUP BY from_acct) as T3   
    ON T2.to_acct = T3.from_acct 
  ;

your question is pretty clear, but please add to the question what queries you have tried already, and in what way they did not work; that helps others know how much explanation they should provide with their answers, and helps others focus their answers on things you have not tried, and/or suggest adjustments to what you've already done — landru27
– landru27, Commented Nov 11, 2018 at 19:18
Join the accounts table with the transactions table ON id IN (from_acct, to_acct). Add the opening balance to the sum of transfers in and the sum of the negative of transfers out. — Barmar
– Barmar, Commented Nov 11, 2018 at 19:57

Michael · Accepted Answer · 2018-11-11 20:05:00Z

1

Something like this. If you'd provided more data, answer could be more precise. Table1 is your first table, Table2 is the second.

select acct_name as acct,
opening_bal - t1.put_money + t2.get_money as balance
from Table1
left join (select from_acct, ifnull(sum(amount),0) as put_money from Table2 group by from_acct) t1
on t1.from_acct = Table1.id
left join (select to_acct, ifnull(sum(amount),0) as get_money from Table2 group by to_acct) t2
on t2.to_acct = Table1.id;

edited Nov 11, 2018 at 20:05

answered Nov 11, 2018 at 19:59

Michael

5,3452 gold badges16 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL multiple sums, using numbers from multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related