SQL 如何对 GROUP BY 中的多个条件的列求和

How to SUM columns on multiple conditions in a GROUP BY

提问by David

I am trying to return a list of Accounts with their Balances, Outcome and Income

我正在尝试返回包含余额、结果和收入的帐户列表

Account            Transaction
-------            -----------
AccountID          TransactionID
BankName           AccountID
Locale             Amount
Status

Here is what I currently have. Could someone explain where I am going wrong?

这是我目前拥有的。有人可以解释我哪里出错了吗？

select
    a.ACCOUNT_ID,
    a.BANK_NAME,
    a.LOCALE,
    a.STATUS,
    sum(t1.AMOUNT) as BALANCE,
    sum(t2.AMOUNT) as OUTCOME,
    sum(t3.AMOUNT) as INCOME
from ACCOUNT a
left join TRANSACTION t1 on t1.ACCOUNT_ID = a.ACCOUNT_ID
left join TRANSACTION t2 on t2.ACCOUNT_ID = a.ACCOUNT_ID and t2.AMOUNT < 0
left join TRANSACTION t3 on t3.ACCOUNT_ID = a.ACCOUNT_ID and t3.AMOUNT > 0
group by a.ACCOUNT_ID, a.BANK_NAME, a.LOCALE, a.[STATUS]

UPDATE

更新

Have corrected the t2 left join syntax as per the comment below.

根据下面的评论更正了 t2 左连接语法。

The output I am expecting is hopefully obvious from the question. For 6 accounts, the SQL should return 6 accounts with their Balance, Income and Outcome of that account.

希望从问题中可以明显看出我期望的输出。对于 6 个帐户，SQL 应返回 6 个帐户以及该帐户的余额、收入和结果。

The problem with the SQL I provided was that the numbers are wrong! As per the comments I think the problem stems from joining multiple times which is summing the amounts incorrectly.

我提供的 SQL 的问题是数字错误！根据评论，我认为问题源于多次加入，这对金额的总和不正确。

回答by outis

Since you didn't tell us what's going wrong(that is, describe the behavior you get in addition to describing the the behavior you expect), it's hard to say where, but there are a couple of possibilities. Neil points out one. Another is that since you join on the transaction table three times, you're pairing transactions with transactions and getting repetitions. Instead, join on the transaction table a single time and change how you sum up the Amountcolumn.

由于您没有告诉我们出了什么问题（也就是说，除了描述您期望的行为之外，还描述了您获得的行为），因此很难说出在哪里，但有几种可能性。尼尔指出了一个。另一个原因是，由于您在事务表上加入了 3 次，因此您将事务与事务配对并获得重复。相反，在事务表上加入一次并更改您总结Amount列的方式。

Select
    a.ACCOUNT_ID,
    a.BANK_NAME,
    a.LOCALE,
    a.STATUS,
    sum(t.AMOUNT) as BALANCE,
    sum((t.AMOUNT < 0) * t.AMOUNT) As OUTGOING,
    sum((t.AMOUNT > 0) * t.AMOUNT) As INCOMING
From ACCOUNT a
Left Join TRANSACTION t On t.ACCOUNT_ID = a.ACCOUNT_ID
Group By a.ACCOUNT_ID, a.BANK_NAME, a.LOCALE, a.[STATUS]

You can use CASEexpressions as a more readable alternative to the multiplications:

您可以使用CASE表达式作为乘法的更易读的替代方法：

Select
    a.ACCOUNT_ID,
    a.BANK_NAME,
    a.LOCALE,
    a.[STATUS],
    sum(t.AMOUNT) As BALANCE,
    sum(CASE WHEN t.AMOUNT < 0 THEN t.AMOUNT ELSE 0 end) As OUTCOME,
    sum(CASE WHEN t.AMOUNT > 0 THEN t.AMOUNT ELSE 0 end) As INCOME
From ACCOUNT a
Left Join [TRANSACTION] t On t.ACCOUNT_ID = a.ACCOUNT_ID
Group By a.ACCOUNT_ID, a.BANK_NAME, a.LOCALE, a.[STATUS]

回答by Mitch Wheat

Did you mean:

你的意思是：

select 
    a.ACCOUNT_ID, 
    a.BANK_NAME, 
    a.LOCALE, 
    a.STATUS, 
    sum(t1.AMOUNT) as BALANCE, 
    sum(CASE WHEN t2.AMOUNT < 0 THEN t2.Amount ELSE 0 END) as OUTCOME, 
    sum(CASE WHEN t3.AMOUNT > 0 THEN t3.Amount ELSE 0 END) as INCOME 
from 
    ACCOUNT a 
    left join TRANSACTION t1 on t1.ACCOUNT_ID = a.ACCOUNT_ID 
    left join TRANSACTION t2 on t2.ACCOUNT_ID = a.ACCOUNT_ID
    left join TRANSACTION t3 on t3.ACCOUNT_ID = a.ACCOUNT_ID
group by 
    a.ACCOUNT_ID, a.BANK_NAME, a.LOCALE, a.[STATUS] 

回答by Thomas

I'm not sure why you would need the multiple joins. Couldn't you simply do something like:

我不确定您为什么需要多个连接。你不能简单地做这样的事情：

Select
    a.ACCOUNT_ID
    , a.BANK_NAME
    , a.LOCALE
    , a.STATUS
    , Sum ( t.Amount ) As Balance
    , Sum( Case When t.Amount < 0 Then Amount End ) As Outcome
    , Sum( Case When t.Amount > 0 Then Amount End ) As Income
From ACCOUNT a
    Left Join TRANSACTION t 
        On t.ACCOUNT_ID = a.ACCOUNT_ID
Group By a.ACCOUNT_ID, a.BANK_NAME, a.LOCALE, a.[STATUS]

回答by Neil Moss

The join for TRANSACTION t2 should be on t2as in on t2.ACCOUNT_ID = a.ACCOUNT_ID

TRANSACTION t2 的连接应该在 t2 上，如on t2.ACCOUNT_ID = a.ACCOUNT_ID