I want to get the average amount of unique shops that clients do the shopping in.

My table is called TRANSACTIONS and has the following columns:

• CLIENT NUMBER
• RETAILER
• DATA
• TRANSACTION VALUE

I have this right now but I don't know how to proceed, I need to do R/C My query now:

SELECT
  count(distinct CLIENT NUMBER) As C, 
  count(distinct RETAILER) As R 
FROM TRANSACTIONS
group by CLIENT NUMBER

CodePudding user response：

This is two steps:

1. Get the number of unique shops per client.
2. Get the average of that number.

The query:

select avg(number_of_shops)
from
(
  select client_number, count(distinct retailer) as number_of_shops
  from transactions
  group by client_number
) clients_with_shop_count;