SQL – Distribution Count

Hi I have the following table:

 crm_id  | customer_id

jon         12345
jon         12346
ben         12347
sam         12348

I would like to show the following:

Crm_ID count | Number of customer_ids
1                    2
2                    1

Basically I want to count the number crm_ids that have 1,2,3,4,5+ customer_ids.

Thanks

Answer

One approach is to aggregate twice. First, aggregate over crm_id and generate counts. Then, aggregate over those counts themselves and generate a count of counts.

SELECT
    cnt AS crm_id_cnt,
    COUNT(*) AS num_customer_ids
FROM
(
    SELECT crm_id, COUNT(DISTINCT customer_id) AS cnt
    FROM yourTable
    GROUP BY crm_id
) t
GROUP BY cnt;

Have a look at a demo below, given in MySQL as you did not specify a particular database (though my answer should run on most databases I think).

Demo