I have the following table, in an Azure SQL DB that has duplicate values that I'm trying to Sum.
Here is the Logic: If the PaymentID is unique, then Sum Payment, If the creditID is unique, then sum credit, if the debitid is unique, then sum debit. And take the max(source) The idea is to get a single line, per ID with distinct values for the ID summed.
158464166 | -185.15 | 0 | -3.45 | CC | 5910551 | 5910548
(in the above - I've taken the min(paymentid) to make it look nicer
Please note that although in the above snippet, Creditid and Debitid only have a duplicate ID, it's possible that they may have distinct IDs, so any code will have to be able to handle that. PaymentID will always be unique.
It is also possible that the values for payment, credit and debit may not be unique (e.g. a payment of $50 is made twice to a single ID), so we can't group on payment.
I got as far as this:
SELECT id, sum(payment), sum(credit), sum(debit), max(source), creditid, debitid FROM ( SELECT *, COUNT(*) OVER (PARTITION BY id) AS cnt FROM Temp_Payment) AS t WHERE t.cnt > 1 group by id, creditid, debitid
but it's not giving the expected outcome.