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

Mysql – How do I get all the rows in the table with MAX value for a COUNT of the same column?

dept_id course_id student_id
CS 101 11
Math 101 11
CS 101 12
CS 201 22
Math 301 22
EE 102 33
Math 201 33

This is the current sql table called "enrolled" and
I need to select all the departments with the highest number of enrolments.

I tried

SELECT dept_id,COUNT(dept_id) as "enrollments" 
FROM enrolled 
GROUP BY dept_id;

to get the number of enrollments for each department. But then I am unsure on how to get all the departments with the maximum enrollment.

The final result should be a single column with "CS" and "Math".

Tags:

3

Answers


  1. 0

    You may use DENSE_RANK function to give a rank for each department according to the count of enrolled students as the following:

    SELECT dept_id
FROM
(
  SELECT dept_id, DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) rnk
  FROM enrolled
  GROUP BY dept_id
) T
WHERE rnk=1

    See a demo.

    Login or Signup to reply.
  2. 0

    You can use HAVING COUNT clause if you have older MySQL version which doesn’t support windows functions

    select dept_id 
from  enrolled 
group by dept_id
having count(dept_id) =  ( select max(tot_count) 
                           from ( select count(dept_id) as tot_count 
                                  from  enrolled 
                                  group by dept_id
                                 ) tbl
                          );

    https://dbfiddle.uk/sNcMzVxe

    The following query will return the max count which is then used in the having clause

      select max(tot_count) 
  from ( select count(dept_id) as tot_count 
         from  enrolled 
         group by dept_id
        ) tbl

    In simple words in your case it will be having count(dept_id) = 3;

    Login or Signup to reply.
  3. 0
    • First you need to calculate the total score based on each department, which we did in CTE 1 using GROUP BY
    • then you need to create a rank based on the highest score (descending order), if the scores can be tied you need to create a rank based on DENSE_RANK(). This would always generate the same rank for the same values, in your case it is looking at the total_scores from the the step 1, in the highest oder (ORDER BY is set to desc). If you want to do the query other way around you can remove DESC and the window function will by default work in ASC order
    • In the last you filter on the rank = 1 using where clause to get the highest enrolled departments
    
with main as (
  select dept_id, count(student_id) as total_enrolled_students
  from enrolled
  group by 1
  ),ranking as (
  select *, 
  dense_rank() over(order by total_enrolled_students desc) as rank_
  from main
  )
select * from ranking where rank_ = 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search