Count by number of values-CodePudding

I have a table structure of TableA like :

-------------------
Id      |   Type
-------------------
12345   |   RegA
12345   |   RegB
23456   |   RegB
45678   |   RegB
45678   |   RegA
-------------------

I want to obtain how many IDs are having how many Types each. In the above case, 1 ID is having 1 distinct Type, whereas 2 IDs are having 2 distinct Types each. Hence, the output should be like below:

---------------------------
Id_Count    |   Type_Count
---------------------------
2           |   2
1           |   1
---------------------------

I'm able to receive the count by individual IDs like the below. But not able to retrieve like the above mentioned.

-------------------------------
Id_Count        |   Type_Count
-------------------------------
12345           |   2
45678           |   2
23456           |   1
-------------------------------

CodePudding user response：

you could use count(dictinct type) as result for an outer count

 select count(*), type_count
 from (
 select id , count(distinct   Type) type_count
 from my_table  
 group by id ) t
 group by type_count

CodePudding user response：

You can achieve this by using a second aggregation on your first result (in the subquery) eg

SELECT
    COUNT(1) as Id_Count,
    Type_Count
FROM (
    SELECT
        Id,
        COUNT(1) as Type_Count
    FROM 
        TableA
    GROUP BY
        Id
) t
GROUP BY Type_Count

Edit 1: As highlighted by @mathguy in the comments, if there are duplicate entries in your real dataset the following modification using COUNT(DISTINCT type) will count each occurrence of type only once in each group and similarly for id in COUNT(DISTINCT id) may be more suitable

SELECT
    COUNT(DISTINCT Id) as Id_Count,
    Type_Count
FROM (
    SELECT
        Id,
        COUNT(DISTINCT Type) as Type_Count
    FROM 
        TableA
    GROUP BY
        Id
) t
GROUP BY Type_Count

CodePudding user response：

Try this :

    SELECT 
      Id AS Id_Count, 
      COUNT(*) as Type_Count 
    FROM 
      my_table
    GROUP BY 
      type 
    ORDER BY 
      Type_Count ASC

CodePudding user response：

If you have the option of using a window function

select distinct 
       count(id) over (partition by count(distinct type)) as id_count, 
       count(distinct type) as type_count
from t
group by id;