sql - 收集组合值的总和

Question

我正在尝试执行查询以显示客户余额以及几个客户相关费用的总金额，以便我可以检查总余额是否与总余额匹配。

我想我需要在我的查询中使用 GROUP BY 但到目前为止它已被证明是不成功的。我的尝试如下:

select c.ID, m.Balance, SUM(chg.Balance) as CombinedBalance
from Customer c
INNER JOIN ChargeTemplate chg
on c.ID = chg.CustomerID
WHERE c.Balance <> (SELECT SUM(Balance) FROM ChargeTemplate WHERE chg.CustomerID= c.ID )
GROUP BY c.ID, c.Balance
order by c.ID ASC

上述查询输出总余额和总组合余额，但也显示了 c.Balance 所在的记录。等于综合余额。

即使我删除了 WHERE 子句，下面的查询与上面的查询完全相同。

select c.ID, c.Balance, SUM(chg.Balance) as CombinedBalance
from Customer c
INNER JOIN ChargeTemplate chg
on c.ID = chg.CustomerID
GROUP BY c.ID, c.Balance
order by c.ID ASC

我想要的输出是 CUSTOMERTABLE.BALANCE value 与相应的客户费用总和 - 这样，我可以将实际余额与合并金额进行比较。我怎样才能使这成为现实？

注意:chargetable 有一个 CustomerTable.CustomerID FK



客户 1:

CUSTOMERTABLE.TOTAL_BALANCE = 100.00
CHARGETABLE.CHARGE[1].BALANCE = 20.00
CHARGETABLE.CHARGE[2].BALANCE = 30.00
CHARGETABLE.CHARGE[3].BALANCE = 50.00

客户 2:

CHARGETABLE.CHARGE[1].BALANCE = 10.00
CHARGETABLE.CHARGE[2].BALANCE = 20.00
CHARGETABLE.CHARGE[3].BALANCE = 30.00
CHARGETABLE.CHARGE[4].BALANCE = 90.00
CHARGETABLE.CHARGE[5].BALANCE = 50.00

Answer 1

要过滤聚合，请使用 HAVING 子句

Select
    c.ID, m.Balance, Sum(chg.Balance) as CombinedBalance
From 
    Customer c
        Inner Join
    ChargeTemplate chg
        On c.ID = chg.CustomerID
Group By 
    c.ID, c.Balance
Having 
    m.Balance != Sum(chg.Balance)
Order By
    c.ID ASC