X cost amount grouped by Y
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 this:
Customer Total Discount
--------------------------
A 30.3
B 70.7
So basically I want a club with discounts for each customer per transaction because they are sometimes repeated in my data.
+3
source to share
4 answers
You can use a subquery to get only all single 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 are cases of the same customer, the same transaction, but with a different discount, you will have to decide whether to treat it as a different transaction at all or get only the highest discount or the lowest discount.
To get the maximum 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
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 are going to treat it as a completely different transaction (that is, it will also be added to the total); no further code changes required.
+3
source to share