2

I am trying to create a MySQL query to return the sum of all statement balances for each account in my DB. The query looks like this:

SELECT SUM(balance), handle FROM statement_versions
INNER JOIN statements ON statement_versions.statement_id = statements.id
INNER JOIN accounts ON statements.account_id = accounts.id;

When I do this, it returns only one row with one summed balance and one account (account with ID 1). What I want is to return all accounts with their summed statement balance. How would I achieve this?

develpr
  • 1,296
  • 4
  • 22
  • 42

1 Answers1

6

You need to group by something, probably handle (I presume that is related to the account id?), otherwise MySQL will SUM all the values selected from your JOIN. Adding a GROUP BY clause makes the SUM happen for each distinct value of the column in the GROUP BY. Change your query to:

SELECT SUM(balance), handle FROM statement_versions
INNER JOIN statements ON statement_versions.statement_id = statements.id
INNER JOIN accounts ON statements.account_id = accounts.id
GROUP BY handle;

If handle is not related to accounts.id and you want to get the results grouped by accounts.id, change the query to:

SELECT SUM(balance), accounts.id FROM statement_versions
INNER JOIN statements ON statement_versions.statement_id = statements.id
INNER JOIN accounts ON statements.account_id = accounts.id
GROUP BY accounts.id;
Nick
  • 138,499
  • 22
  • 57
  • 95
  • Sweet thank you! I'm actually trying to figure out how to translate this into Laravel query builder, but thought if I got the underlying query correct I'd be able to do so. I was wrong haha. – develpr Oct 27 '18 at 05:44
  • Well at least this should be the correct underlying query! [This](https://stackoverflow.com/questions/39369044/write-a-query-join-sum-groupby-in-laravel) and [this](https://stackoverflow.com/questions/46641312/laravel-5-inner-join-and-group-by-query-syntax-error) might help with the laravel part. – Nick Oct 27 '18 at 05:47
  • Thank you. I'm getting an error `Column not found: 1054 Unknown column 'statement_versions.balance' in 'field list' (SQL: select sum(`statement_versions`.`balance`) as aggregate from `accounts`)`, so I'm not sure what is going on. It seems for some reason the joins aren't structured properly or something. – develpr Oct 27 '18 at 06:00
  • @hoolakoola is this from your laravel version? You should ask another question so that some laravel experts will see it. – Nick Oct 27 '18 at 06:25