Count a count expression?

[#1]

Customer	Sport
A	Rugby
A	Football
A	Tennis
A	Cricket
B	Football
B	Tennis
C	Football
D	Tennis
E	Rugby
F	Tennis
F	Football
G	Tennis

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

[erichshiino]

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

[rbecher · Accepted solution]

This works:

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

- Ralf

[#4]

Hello,

and just another suggestion:

Regards, Roland

[#5]

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

[thanstad]

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

/Tormod