How to sum duplicated values in a Group(SSRS 2005)

Question

How to sum duplicated values in a Group*(SSRS 2005).*

eg. My query returns values like the following:

CusID Discount Amount
1        20      1000
1        20      2000
1         5       700
2        15      1500
2        15      3000

But,when I sum Discount amount in Group Footer, I cannot get the total values like below. I get 45 for CusID 1 instead of 25. Please help me to solve this problem.Thanks.

CusID Discount Amount
1        20      1000
1        20      2000
1         5       700
------------------------
Total    25      3700

2        15      1500
2        15      3000
------------------------
Total    15      4500

Answer 1

well without what your actual data looks like, i can only provide you a code example based off of the data you provided.

declare @table table (CustID int, Discount int, Amount int)


    insert into @table (CustID,Discount,Amount)
    select 1 as CusID,20 as Discount,1000 as Amount
    union all
    select 1,20,2000
    union all
    select 1,5,700
    union all
    select 2,15,1500
    union all
    select 2,15,3000

    select
        CustID,
        sum(Discount) as Discount,
        sum(Amount) as Amount
    from
    (
        select
            CustID,
            Discount,
            SUM(Amount) as Amount
        from @table
        group by CustID, Discount
    ) a
    group by CustID

Answer 2

One slight simplification to DForcek42's answer by using the sum(distinct x)

declare @table table (CustID int, Discount int, Amount int)

insert into @table (CustID,Discount,Amount)
select 1 as CusID,20 as Discount,1000 as Amount
union all
select 1,20,2000
union all
select 1,5,700
union all
select 2,15,1500
union all
select 2,15,3000

select
    CustID,
    sum(distinct Discount),
    SUM(Amount) as Amount
from @table
group by CustID