I am trying to execute a query to display the customer balance along with the combined amount of several fees associated with the customer, so I can check if the total balance matches the combined balance.
It seems to me that I need to use GROUP BY in my query, but so far this has failed. My attempt is this:
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
The above query displays the total balance and the total combined balance, but also displays entries where c.Balance is equal to the combined balance.
The following query does the same as the above query, although I deleted the WHERE clause.
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
My desired result is the value of CUSTOMERTABLE.BALANCE with the corresponding total amount of customer fees - so I can compare the actual balance, which coincides with the total amount. How can I make this a reality?
Note. The charger has CustomerTable.CustomerID FK
Client 1:
CUSTOMERTABLE.TOTAL_BALANCE = 100.00 CHARGETABLE.CHARGE[1].BALANCE = 20.00 CHARGETABLE.CHARGE[2].BALANCE = 30.00 CHARGETABLE.CHARGE[3].BALANCE = 50.00
Client 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
source share