CODEWITHSUNDEEP
sql
Kev
Kev

Reputation: 773

Collect the sum of combined values

I am trying to execute a query to display a customers balance along with the combined amount of several customer related charges so I can check if the total balance matches the combined balance.

I think I need to use a GROUP BY in my query but it has proved unsuccessful so far. My attempt is as follows:

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 outputs the total balance and the total combined balance but it also displays the records where the c.Balance is equal to the Combined balance.

The below query does the exact same thing as the above query, even though I have removed 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 output is the CUSTOMERTABLE.BALANCE value with the corresponding combined total of the customers charges - this way, I can compare the actual balance matches the combined amount. How can I make this a reality?

Note: The chargetable has a CustomerTable.CustomerID FK

enter image description here

Customer 1: 

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

Customer 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

Upvotes: 4

Views: 148

Answers (1)

Laurence
Laurence

Reputation: 10976

To filter on aggregates, use the HAVING clause

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

Upvotes: 3

Related Questions