mysql sum输出结果,为什么MySQL SUM查询仅返回一行?

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?

解决方案

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;