Question 112874 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Group by Count – Number of Repetitions

I have a table like below,

Name Value
A Sample1
A Sample2
A Sample3
B Sample3
B Sample1
C Sample2
C Sample3
D Sample1

If I group the table by Name to get the count,

Select Name, Count(*) as count from table group by Name;

I will get the following result,

Name count
A 3
B 2
C 2
D 1

I need to get the number of repetitions of each count. Means desired outcome below,

count numberOfTimes
3 1
2 2
1 1
Tags:

2

Answers


  1. 0

    Toss your existing sql into a subquery and group once again:

    SELECT ncount, count(*) numberOfTimes
FROM (Select Name, Count(*) as ncount from table group by Name) dt
GROUP BY ncount
    Login or Signup to reply.
  2. 0

    Use COUNT(*) window function in your query:

    SELECT DISTINCT COUNT(*) AS count,
       COUNT(*) OVER (PARTITION BY COUNT(*)) AS numberOfTimes
FROM tablename 
GROUP BY Name;

    See the demo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search