SQL SUM GROUP BY两个表 [英] SQL SUM GROUP BY two tables
问题描述
我很难编写一个SQL查询，它将正确地将account_no分组在一起并减去一笔金额。
首先，我写了这个查询，它更新了除ACCOUNT_NO A之外的所有内容-102应该最终为4500而不是两个不同的正确余额。
select transactions.account_no，account.balance，transactions.amount ，
（account.balance + transactions.amount）asCORRECT BALANCE
来自交易，账户
where account.account_no = transactions.account_no;
ACCOUNT_NO |平衡| AMOUNT |正确性
A-102 | 4000 | 2000 | 6000
A-102 | 4000 | -1500 | 2500
A-222 | 8000 | -1000 | 7000
A-305 | 2000 | 1300 | 3300
我试图对account_no进行求和和分组，但我无法解决如何使用两张桌子。这只是我尝试过的，但无法工作。
select transactions.account_no，SUM（transactions.amount）
来自交易
group by transactions.account_no;
ACCOUNT_NO | SUM（TRANSACTIONS.AMOUNT）
A-305 | 1300
A-102 | 500
A-222 | -1000
预期结果应该是：
account_no上|平衡| AMOUNT |正确性
A-102 | 4000 | 500 | 4500
A-222 | 8000 | -1000 | 7000
A-305 | 2000 | 1300 | 3300
这是因为账户A-102有两个不同的金额出来，但是从相同的为您的查询，要将两行分组在一行上，您可以尝试对帐号AND余额：
SELECT T.account_no
，A.balance
，SUM（T.amount） AS TotalAmount
，（A.balance + SUM（T.amount））ASCORRECT BALANCE
FROM transactions AS T
INNER JOIN account AS A ON T.account_no = A.account_no
GROUP BY T.account_no，A.balance;
（顺便说一句，我使用了ANSI连接而不是旧连接方式因为它更清楚你在做什么。）
编辑
为了让事情更清楚些，我已经制作了一个 SQL小提琴 。这是否代表你的情况或多或少正确？
EDIT2
以上查询不会显示任何没有交易的帐户，如 Kaf 所评论的。这可能是你想要的，但如果不是，你可以像这样切换连接表：
SELECT A.account_no
，A.balance
，SUM（T.amount）AS TotalAmount
，（A.balance + SUM（T.amount））asCORRECT BALANCE
FROM account A
LEFT OUTER JOIN交易AS T ON T.account_no = A.account_no
GROUP BY A.account_no，A.balance;
I'm having difficulty writing an SQL query that will correctly group account_no together and subtracting an amount.
Firstly I wrote this query which updates everything fine except ACCOUNT_NO A-102 should end up as 4500 not as two different correct balances.
select transactions.account_no, account.balance, transactions.amount,
(account.balance + transactions.amount) AS "CORRECT BALANCE"
from transactions, account
where account.account_no = transactions.account_no;
ACCOUNT_NO| BALANCE | AMOUNT | CORRECTBALANCE
A-102 | 4000 | 2000 | 6000
A-102 | 4000 | -1500 | 2500
A-222 | 8000 | -1000 | 7000
A-305 | 2000 | 1300 | 3300
I tried to sum and group by the account_no but I cannot work out how to do this with the two tables. This was just something I tried but could not get to work.
select transactions.account_no, SUM(transactions.amount)
from transactions
group by transactions.account_no;
ACCOUNT_NO| SUM(TRANSACTIONS.AMOUNT)
A-305 | 1300
A-102 | 500
A-222 | -1000
The expected outcome should be:
ACCOUNT_NO| BALANCE | AMOUNT | CORRECTBALANCE
A-102 | 4000 | 500 | 4500
A-222 | 8000 | -1000 | 7000
A-305 | 2000 | 1300 | 3300
This is because the account A-102 it has two different amounts coming out, but from the same balance.
For your query, to get the two rows grouped on one row, you can try grouping on the account number AND the balance:
SELECT T.account_no
,A.balance
,SUM(T.amount) AS TotalAmount
,(A.balance + SUM(T.amount)) AS "CORRECT BALANCE"
FROM transactions AS T
INNER JOIN account AS A ON T.account_no = A.account_no
GROUP BY T.account_no, A.balance;
(By the way, I've used the ANSI join instead of the 'old' way of joining tables, because it's much more clear what you're doing that way.)
EDIT
To make things a bit more clear, I've made a SQL Fiddle. Does this represent your situation more or less correctly?
EDIT2
The above query would not show any accounts without transactions, as Kaf commented. That might be what you want, but in case it's not you can switch the join tables like this:
SELECT A.account_no
,A.balance
,SUM(T.amount) AS TotalAmount
,(A.balance + SUM(T.amount)) AS "CORRECT BALANCE"
FROM account AS A
LEFT OUTER JOIN transactions AS T ON T.account_no = A.account_no
GROUP BY A.account_no, A.balance;
这篇关于SQL SUM GROUP BY两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
