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".

CodePudding user response：

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.

CodePudding user response：

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;

CodePudding user response：

• 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;