Exclude duplicates

[#1]

Hi

I have to fields, NumberofChildren and ID.

Because the ID occurs multiple times the sum(numberofchildren) shows wrong. I want to distinct them so it only sum it ones.

And, does that don´t have a children are marked as 0.

How can I do that?

[sreenivas]

In the Expression instead of Sum use ONLY

[its_anandrjs]

Hi,

Use Distinct with Sum function like Sum(Distinct numberofchildren).

Thanks Rgds

[#4]

Hello ,

the possible is two: from script from  report:

from script

TAB:

noconcatenate

LOAD ID,

          NAME,

AVG(Number of cildren)  ------------------------>(OR dISTINCT( nUMBEr OF cILDREN))

Resident INL

group by

                ID,

          NAME;

FROM REPORT:

IN THE EXPRESSION oNLY(Number of cildren) OR  sum(distinct Number of Cildren)

[#5]

=Only({$}NumberOfChildren) works but how do I then sum them? I´d like to se the total number of children.

Edit: sum(distinct Number of Cildren) wont work because the it only count the value of say 2 children ones, but several person can have 2 children.

If 2 people have 2 children I want to get the result of 4. The problem is that I some how have to distinct the ID in the sum so it only count the children ones per person.

[swuehl · Accepted solution]

Try

=sum({$-< NumberOfChildren = {0} >} aggr(NumberOfChildren, ID))

[#7]

Why not do something simple like : -

Sum(NumberOfChildren) / Count(ID)

[#8]

Thanks swuehl. Thats perfect!