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