declare @table table (Customer  char(1), Transaction char(3), Discount float);
insert into @table values 
('A', '001', '10.1'),
('A', '001', '10.1'),
('A', '002', '20.2'),
('B', '003', '30.3'),
('B', '004', '40.4')

I am trying to do something like this:

SELECT Customer, (SELECT SUM(Discount) WHERE Transaction IS DISTINCT)
FROM @table
GROUP BY Customer

And the result should look like:

Customer    Total Discount
--------------------------
A                   30.3       
B                   70.7

So basically I need to club all the discounts for every customer per transaction because they are sometimes repeated in my data.

You can use a subquery to get only all distinct rows;

SELECT Customer, SUM(Discount) as Total_Discount FROM 
(
 SELECT DISTINCT Customer, Transaction, Discount FROM @table
) x
group by Customer

In response to your question; in case there'd be cases of same customer, same transaction, but different discount, you'd have to decide whether to treat it as a different transaction altogether, or get only the HIGHEST discount or the LOWEST discount.

For getting the highest discount,

SELECT Customer, SUM(Discount) as Total_Discount FROM 
(
 SELECT Customer, Transaction, MAX(Discount) as Discount FROM @table
 GROUP BY Customer, Transaction
) x
group by Customer

For getting the lowest discount

SELECT Customer, SUM(Discount) as Total_Discount FROM 
(
 SELECT Customer, Transaction, MIN(Discount) as Discount FROM @table
 GROUP BY Customer, Transaction
) x
group by Customer

If you're going to treat it as an entirely different transaction (meaning it'd also be added to the total) ; no need for further code change.

First take DISTINCT value from your temp table based on 3 columns.Then SUM discount value based on GROUP BY Customer

  SELECT A.Customer, SUM(A.Discount) as Total_Discount 
  FROM 
   (
     SELECT DISTINCT Customer, Transaction, Discount FROM @table
   ) A
  GROUP BY A.Customer

Using Row number

SELECT Customer
    ,sum(Discount) as Total_Discount 
FROM (
    SELECT Customer
        ,[Transaction]
        ,Discount
        ,row_number() OVER (
            PARTITION BY Customer
            ,[Transaction] ORDER BY Discount
            ) AS rn
    FROM @table
    ) t
WHERE rn = 1
GROUP BY Customer

Get Distinct Records from table throuth Inline Query and named as "Inline" and then select Customer and Sum Discount from "Inline" Like

SELECT Inline.Customer,
SUM(Inline.[Discount]) FROM
(SELECT DISTINCT Customer,[Discount] FROM @table)   Inline
  GROUP BY Inline.Customer

• The subquery can be used to get only all distinct rows.

SELECT Customer, SUM(Discount) as Total_Discount FROM 

(

 SELECT DISTINCT Customer, Transaction, Discount FROM @table

) x

group by Customer​

 

• In case of your question, there would be cases of the same customer, same transaction, but different discount, you'd have to decide whether to treat it as a different transaction altogether or get only the HIGHEST discount or the LOWEST discount.

• In order to get the highest discount,

SELECT Customer, SUM(Discount) as Total_Discount FROM 

(

 SELECT Customer, Transaction, MAX(Discount) as Discount FROM @table

 GROUP BY Customer, Transaction

) x

group by Customer​

 

• In order to get the lowest discount,

SELECT Customer, SUM(Discount) as Total_Discount FROM 

(

 SELECT Customer, Transaction, MIN(Discount) as Discount FROM @table

 GROUP BY Customer, Transaction

) x

group by Customer​

If you're going to treat it as an entirely different transaction (i.e It would also be added to the total), there is no need for the further code change.