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 club all the discounts for every customer per transaction, because they are sometimes repeated in my data.

what would happen if they have the same Customer and Transaction But different Discount? or would that not happen? — beejm, Mar 30 '17 at 09:18
@BennjoeMordeno For now I don't see that in the data. That would probably be a data error, I think. — Christa, Mar 30 '17 at 09:20

beejm · Accepted Answer · 2017-03-30T09:44:35.187

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.

Thanks Bennjoe! That's perfect. Suppose I have the same Customer and Transaction but a different Discount, and I have it add the different amounts too, how much would the code change? Thanks again! — Christa, Mar 30 '17 at 09:27

score 2 · Answer 2 · answered Mar 30 '17 at 09:23

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

Shiju Shaji · Answer 3 · 2017-03-30T09:28:48.423

0

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

edited Mar 30 '17 at 09:28

answered Mar 30 '17 at 09:23

Shiju Shaji

1,682
17
24

score 0 · Answer 4 · answered Mar 30 '17 at 09:32

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

Sum of value per X grouped by Y

4 Answers4