MySQL query - show how many students enrolled grouped by couses-CodePudding

I have the following table:

 ------------- ------------- 
| student_id  | course_code |
 ------------- ------------- 
|1            |          100|
|2            |          100|
|3            |          100|
|1            |          200|
|2            |          300|
|3            |          400|
|2            |          500|
|3            |          200|
|3            |          500|
 ------------- -------------

I want to return a query that returns the number of students who are enrolled in at least 3 courses and show the output in descending order:

 ------------- ------------- 
| student_id  | course_code |
 ------------- ------------- 
|2            |          500|
|3            |          500|
|3            |          400|
|2            |          300|
|3            |          200|
|2            |          100|
|3            |          100|
 ------------- -------------

This is my SQL. Why doesn't it count only the student_id column? My SQL is counting the course_code column instead. What would be the appropriate SQL?

SELECT course_code, COUNT(student_id)
FROM student
GROUP BY course_code
HAVING COUNT(student_id) >= 3
ORDER BY course_code DESC;

Demo

CodePudding user response：

you're counting the number of students in each course code, not the number of courses that each student is enrolled in.

You need to join the grouped query with the original table to get all the records for each student.

select t1.*
FROM student AS t1
JOIN (
    SELECT student_id
    FROM student
    GROUP BY student_id
    HAVING COUNT(*) >= 3
) AS t2 ON t1. student_id = t2.student_id
ORDER BY t1.course_code DESC