7 Replies Latest reply: Aug 22, 2012 3:56 AM by Göran Hofstedt

# Exclude duplicates

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?

• ###### Re: Exclude duplicates

In the Expression instead of Sum use ONLY

• ###### Re: Exclude duplicates

=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.

• ###### Re: Exclude duplicates

Try

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

• ###### Re: Exclude duplicates

Hi,

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

Thanks Rgds

• ###### Re: Exclude duplicates

Hello ,

the possible is two: from script from  report:

from script

TAB:

noconcatenate

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)

• ###### Re: Exclude duplicates

Why not do something simple like : -

Sum(NumberOfChildren) / Count(ID)

• ###### Re: Exclude duplicates

Thanks swuehl. Thats perfect!