SQL Notes: Classes More Than 5 Students

596. Classes More Than 5 Students

Problem

There is a table courses with columns: student and class

Please list out all classes which have more than or equal to 5 students.

For example, the table:

+---------+------------+
| student | class      |
+---------+------------+
| A       | Math       |
| B       | English    |
| C       | Math       |
| D       | Biology    |
| E       | Math       |
| F       | Computer   |
| G       | Math       |
| H       | Math       |
| I       | Math       |
+---------+------------+

Should output:

+---------+
| class   |
+---------+
| Math    |
+---------+

Note:
The students should not be counted duplicates in each course.

Solution

Algorithm

The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”. The GROUP BYstatement is often used with aggregate functions COUNT(), MAX(), MIN(), SUM(), AVG() to group the result-set by one or more columns.

MySQL

select class 
from courses
group by class having count(distinct student) > 4;

Reference

https://leetcode.com/problems/not-boring-movies/