Sum Field B dependant of DISTINCT value in Field A

[nwilliams]

Hi,

I have a requirement to sum the values in field B, but only require those where the value in column A is distinct.  Example:

The table below I would like the sum of column B = 289 and not = 415, hence it would remove the values in Red. Due to other data in the table, I can't remove the duplicates in the load script.  Has anyone got any ideas?

A	B
11225	52
11226	14
11226	14
11227	35
11228	55
11229	27
11229	27
11230	6
11231	85
11231	85
11232	15

[sunny_talwar · Accepted solution]

May be this

Sum(Aggr(B, A))

or

Sum(Aggr(Only(B), A))

or

Sum(Aggr(Avg(B), A))

or

Sum(Aggr(Sum(DISTINCT B), A))

[pgriffiths]

Sum(B) / Count(A)

[Anonymous]

Why can't you remove dups in the loads script?  Sunny's should work for you but cleaning up the data seems like it would be the best solution.