Counting Things by frequency (i.e. filtering and aggregating data)

I built loyalty app where each final client checks-in at a Merchant.
Each check-in is a Thing in a table named Transactions. Each Transactions has a Client ID, a Merchant ID and an Amount Spent.

I want to show in the Merchant’s dashboard a pie chart showing:

  • number of final clients that did only 1 check-in
  • number of final clients that did 2 check-ins
  • number of final clients that did 3 check-ins
  • number of final clients that did 4 check-ins or more

Suppose that, for a certain Merchant, I have 600 clients that performed 1250 check-ins as follows

# check-ins per client # clients total checkins
1 300 300
2 100 200
3 50 150
4 150 600
Total 600 1250

I can Search for Transactions with a certain Merchant ID, group by Client ID and count to get 600.
But how can I get 300? I should filter saying something like “only consider Transactions where Client ID appears once” but I don’t know how to do that.

What if I want to show, instead of the number of clients, the Amount Spent?

Thanks in advance for your help,

Can you please share more details of your database, repeating group setup to make it clearer?
Hopefully someone will help.

I found the solution. I’m posting it here in case other Bubblers need it.

There must be two “group by”:

Search for Transactions with Merchant ID
Group by (Client ID, exact; Aggregation = Count)
Group by (Count, exact; Aggregation = Count)

The first grouping groups by client ID and produces a table with one Client ID per line paired with the number of his check-ins.
The second grouping groups by the number of check-ins and returns a table with number of check-ins in each line paired with number of clients that did such number of check-ins

In the formulas there will be 2 elements “Count” but only the second has to be considered.

Here’s a couple screenshots (Shopper’s phone = Client ID)