Question

596. Classes More Than 5 Students

中文文档

Description

Table: Courses

+-------------+---------+
| Column Name | Type  |
+-------------+---------+
| student   | varchar |
| class     | varchar |
+-------------+---------+
(student, class) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates the name of a student and the class in which they are enrolled.

 

Write a solution to find all the classes that have at least five students.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Courses table:
+---------+----------+
| student | class  |
+---------+----------+
| A     | Math   |
| B     | English  |
| C     | Math   |
| D     | Biology  |
| E     | Math   |
| F     | Computer |
| G     | Math   |
| H     | Math   |
| I     | Math   |
+---------+----------+
Output: 
+---------+
| class   |
+---------+
| Math  |
+---------+
Explanation: 
- Math has 6 students, so we include it.
- English has 1 student, so we do not include it.
- Biology has 1 student, so we do not include it.
- Computer has 1 student, so we do not include it.

Solutions

Solution 1: Grouping and Aggregation

We can use the GROUP BY statement to group by class and then use the HAVING statement to filter out the classes with a student count greater than or equal to $5$.

# Write your MySQL query statement below
SELECT class
FROM Courses
GROUP BY 1
HAVING COUNT(1) >= 5;