Adding multiple rows of data from two separate columns into one column on unique ID

1

If I want to add total payments and total adjustments into one column per unique source ID, how would you do this? This is what I am doing and it isn't totaling correctly.

For example
Source ID=1924576
Total Payments = 102.02
Total Adjustments = 76.06

This data is listed in two different rows with the same source ID....

I wrote this but it isn't totaling correctly:

UPDATE SOS_Transactions
SET SOS_Transactions.total_paidoncharge = b.total_paidoncharge
FROM SOS_Transactions a JOIN
(SELECT DISTINCT practice_id, source_id, SUM (ISNULL(total_payments,0) + ISNULL(total_adjustments, 0)) AS [total_paidoncharge]
FROM SOS_Transactions b Group by practice_id, source_id) b ON a.practice_id = b.practice_id and a.source_id = b.source_id

2

with sample data

image
image.png1293×1334 36.5 KB

2 Likes
3

So this worked great, thank you! i ended up having to add one more unique identifier (the transaction ID) on the grouping as if the same transaction was listed more than once it was calculating it more than once. Thanks once again for your help!! :slight_smile: