为什么MySQL SUM查询仅返回一行? [英] Why is MySQL SUM query only returning one row?

问题描述

我正在尝试创建一个MySQL查询以返回数据库中每个帐户的所有语句余额的总和.查询看起来像这样:

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;

执行此操作时，它仅返回一行，其中有一个总余额和一个帐户(ID为1的帐户).我想要的是退还所有帐户的对帐单余额.我将如何实现呢?

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?

推荐答案

您需要对某些内容进行分组，可能是handle(我想这与帐户ID有关?)，否则MySQL会SUM 所有从您的JOIN中选择的值.添加GROUP BY子句会使SUM发生在GROUP BY中列的每个不同值上.将查询更改为:

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;

如果handle与accounts.id不相关，并且您想获得按accounts.id分组的结果，请将查询更改为:

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;

这篇关于为什么MySQL SUM查询仅返回一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！