Search code examples
sqlsql-serversumduplicatesdistinct

SQL-Server: Summing a column while ignoring duplicate records

I have a table that contains rows with duplicated values.

TransID--CustID-- Date-----Amount
5217151 212671  2020-10-31  30.00
5217151 212671  2020-10-31  30.00
5219330 212671  2020-11-30  30.00
5219330 212671  2020-11-30  30.00

I need to sum the Amount column, but ignore any rows where the transaction ID is duplicated. For example, the sum of Amount for customer 212671 would be 60.00. I have tried to use Select Distinct but I cannot figure out the correct syntax for doing this when using distinct on one column but summing on a different one.

This is my first post on stackoverflow and I'm finding it very difficult to format my text and paste in actual screen shots of the data, so please excuse that. Thank you.

Solution

  • Apply DISTINCT before aggregation:

    with cte as
 (
   select distinct TransID, CustID, Amount
   from mytable
 )
select CustId, sum(Amount)
from cte
group by CustId