5 Replies Latest reply: Oct 22, 2013 2:02 AM by Tormod Hanstad

# Count a count expression?

CustomerSport
ARugby
AFootball
ATennis
ACricket
BFootball
BTennis
CFootball
DTennis
ERugby
FTennis
FFootball
GTennis

Hello, what I want to do is count the number of times a customer only has 1 sport against them, regardless of what the sport actually is. So customer A has 4 sports against them, customer B has 2 sports against them, Customers C-E have only 1 sport against them, customer F has 2 sports against them and customer G has 1 sport against them. So the answer is 4 (only 4 customers have only one sport against them). Essentially its a count of a count, but I have no idea how this is done. Any help will be much appreciated.

Thanks,

Ralph

• ###### Count a count expression?

Hi, Try this (didn`t check, but should look like this)

= sum ( aggr ( if( count( Sport)= 1 , 1, 0)

, Customer ) )

//maybe you should usecount(distinct Sport) - depends on your real table

This will group the data by Customer. For each one of them, it gives one or zero according to the condition.

After this I sum the 1s.

Hope it helps,

Erich

• ###### Count a count expression?

This works:

=count({\$<Customer={"=count(DISTINCT Sport)=1"}>} DISTINCT Customer)

- Ralf

• ###### Re: Count a count expression?

Hello,

and just another suggestion:

Regards, Roland

• ###### Count a count expression?

Many thanks for all these suggestions, they all work as expected. Thanks, Ralph

• ###### Re: Count a count expression?

This solution is very useful for my work where we have different ways of DISTINCT count.

/Tormod