How to Sum non-duplicate values

Hi,
I have to create a report with a data as follows-
Voucher Branch Item ItemAmount
-- -- -- --
V1 Branch1 Item1 100
V2 Branch1 Item1 100
V3 Branch1 Item1 100
V4 Branch1 Item2 50
V5 Branch1 Item2 50
V6 Branch1 Item3 75
V7 Branch2 Item1 150
V8 Branch3 Item5 250
The table should have branch group and Sum(ItemAmount) per Branch.
I need to display ItemAmount totals per Branch but the totals should
consider only distinct ItemAmount per Item.
Total per Branch1 should be 100 + 50 + 75 = 225 When I say
Sum(ItemAmount, "Branch") in Branch group it's calculating 100 + 100 +
100 + 50 + 50 + 75 = 475.
Any suggestions would be appreciated.On Mar 16, 7:18 pm, "Chiru" <uchira...@.gmail.com> wrote:
> Hi,
> I have to create a report with a data as follows-
> Voucher Branch Item ItemAmount
> -- -- -- --
> V1 Branch1 Item1 100
> V2 Branch1 Item1 100
> V3 Branch1 Item1 100
> V4 Branch1 Item2 50
> V5 Branch1 Item2 50
> V6 Branch1 Item3 75
> V7 Branch2 Item1 150
> V8 Branch3 Item5 250
> The table should have branch group and Sum(ItemAmount) per Branch.
> I need to display ItemAmount totals per Branch but the totals should
> consider only distinct ItemAmount per Item.
> Total per Branch1 should be 100 + 50 + 75 = 225 When I say
> Sum(ItemAmount, "Branch") in Branch group it's calculating 100 + 100 +
> 100 + 50 + 50 + 75 = 475.
> Any suggestions would be appreciated.
I would suggest changing the report query (or stored procedure) to
something like this:
SELECT BRANCH, SUM(DISTINCT(ITEMAMOUNT))
FROM TABLE_X
GROUP BY BRANCH
Or, set up a separate dataset that has this query in it and use it for
the sums. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer