Join of two tables with sums of two different fields grouped by CustomerID

Question

Post reply

Join of two tables with sums of two different fields grouped by CustomerID

DaveBriCam

SSCarpal Tunnel

Points: 4298
More actions
June 13, 2016 at 8:53 am

#317123

Thanks in advance for any help on this:
I have two tables in one database both have PeopleID as the key field.
One table is for Charges and the other is for Payments like below:
Charges PeopleID
100.00 54215
125.00 53252
110.00 63225
10.00 54215
10.00 53252
15.00 63225
Payments PeopleID
75.00 54215
25.00 53252
50.00 63225
10.00 54215
25.00 53252
25.00 63225
I need to display from my SQL something like this:
PeopleID totalCharges totalpayments Balance
54215 110.00 85.00 25.00
53252 135.00 50.00 85.00
63225 125.00 75.00 50.00
I tried something like this that failed miserably:
SELECT c.peopleID, SUM(c.ChargeAmount) - SUM(p.Amount) as [Balance]
FROM Charges c inner join Payments p
on c.peopleid = p.peopleid
GROUP BY c.peopleID
Any ideas of the best way to do this in SQL Server 2008?
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

djj SSCoach Points: 18831 More actions · Answer 1

I seem to miss the obvious. Please see answer from Lowell

Lowell SSC Guru Points: 323518 More actions · Answer 2

because there is a one to many relationship between charges and payments, you cannot join the tables directly, i think you need to join the summerization of those tables instead. otherwise, due to the join, some payments seem to get doubled.

/*

peopleIDTotalChargesTotalPaymentsBalance

53252135.0050.0085.00

54215110.0085.0025.00

63225125.0075.0050.00

*/

;WITH Charges([ChargeAmount],[PeopleID])

AS

(

SELECT 100.00, 54215 UNION ALL

SELECT 125.00, 53252 UNION ALL

SELECT 110.00, 63225 UNION ALL

SELECT 10.00, 54215 UNION ALL

SELECT 10.00, 53252 UNION ALL

SELECT 15.00, 63225

),

Payments([Amount],[PeopleID])

AS

(

SELECT 75.00, 54215 UNION ALL

SELECT 25.00, 53252 UNION ALL

SELECT 50.00, 63225 UNION ALL

SELECT 10.00, 54215 UNION ALL

SELECT 25.00, 53252 UNION ALL

SELECT 25.00, 63225

),

NetCharges

AS

(

SELECT SUM([ChargeAmount]) As TotalCharges ,[PeopleID] FROM Charges GROUP BY [PeopleID]

),

NetPayments

AS

(

SELECT SUM([Amount]) As TotalPayments ,[PeopleID] FROM Payments GROUP BY [PeopleID]

)

SELECT c.peopleID,

TotalCharges,

TotalPayments,

TotalCharges - TotalPayments as [Balance]

FROM NetCharges c

inner join NetPayments p

on c.peopleid = p.peopleid

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 3

I'm not following the placing of the literal amounts into the SQL. Shouldn't I just be using field names/:

SELECT 100.00, 54215 UNION ALL

SELECT 125.00, 53252 UNION ALL

SELECT 110.00, 63225 UNION ALL

SELECT 10.00, 54215 UNION ALL

SELECT 10.00, 53252 UNION ALL

SELECT 15.00, 63225

Lowell SSC Guru Points: 323518 More actions · Answer 4

briancampbellmcad (6/13/2016)
I'm not following the placing of the literal amounts into the SQL. Shouldn't I just be using field names/:
SELECT 100.00, 54215 UNION ALL
SELECT 125.00, 53252 UNION ALL
SELECT 110.00, 63225 UNION ALL
SELECT 10.00, 54215 UNION ALL
SELECT 10.00, 53252 UNION ALL
SELECT 15.00, 63225

the first two CTE's are just so i have real data, based on your copy paste in the post.

it's placeholders for your real tables [Charges] and [Payments]

you didn't include any real DDL, so i converted what you had into a demonstrable, tested query.

for you, if the tables already existed, you could just use the bottom two CTE's:

;WITH NetCharges

AS

(

SELECT SUM([ChargeAmount]) As TotalCharges ,[PeopleID] FROM Charges GROUP BY [PeopleID]

),

NetPayments

AS

(

SELECT SUM([Amount]) As TotalPayments ,[PeopleID] FROM Payments GROUP BY [PeopleID]

)

SELECT c.peopleID,

TotalCharges,

TotalPayments,

TotalCharges - TotalPayments as [Balance]

FROM NetCharges c

inner join NetPayments p

on c.peopleid = p.peopleid

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Alan Burstein SSC Guru Points: 61141 More actions · Answer 5

briancampbellmcad (6/13/2016)
I'm not following the placing of the literal amounts into the SQL. Shouldn't I just be using field names

He's putting the example data into CTEs... It's the same as if he showed you the solution like this:

-- Sample Data

DECLARE @charges TABLE ([ChargeAmount] money,[PeopleID] int);

DECLARE @payments TABLE ([Amount] money,[PeopleID] int);

INSERT @charges

SELECT 100.00, 54215 UNION ALL

SELECT 125.00, 53252 UNION ALL

SELECT 110.00, 63225 UNION ALL

SELECT 10.00, 54215 UNION ALL

SELECT 10.00, 53252 UNION ALL

SELECT 15.00, 63225;

INSERT @payments

SELECT 75.00, 54215 UNION ALL

SELECT 25.00, 53252 UNION ALL

SELECT 50.00, 63225 UNION ALL

SELECT 10.00, 54215 UNION ALL

SELECT 25.00, 53252 UNION ALL

SELECT 25.00, 63225;

-- Solution

WITH

NetCharges

AS

(

SELECT SUM([ChargeAmount]) As TotalCharges ,[PeopleID] FROM @Charges GROUP BY [PeopleID]

),

NetPayments

AS

(

SELECT SUM([Amount]) As TotalPayments ,[PeopleID] FROM @Payments GROUP BY [PeopleID]

)

SELECT c.peopleID,

TotalCharges,

TotalPayments,

TotalCharges - TotalPayments as [Balance]

FROM NetCharges c

inner join NetPayments p

on c.peopleid = p.peopleid;

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001