Sum of value per X grouped by Y

Question

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. 

Answer 1

• 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.