This website contains ALL LeetCode **Premium** problems for
**FREE!!**.

All leaked interview problems are collected from Internet.

All leaked interview problems are collected from Internet.

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 duplicate in each course.

b'

\n## Solution

\n

\n#### Approach: Using **sub-query** [Accepted]

\n\n\n\n#### Approach: Using

\n\n

'
\n\n

\n\n

`GROUP BY`

clause and **Intuition**

First, we can count the student number in each class. And then select the ones have more than 5 students.

\n**Algorithm**

To get the student number in each class. We can use `GROUP BY`

and `COUNT`

, which is very popular used to statistic bases on some character in a table.

SELECT\n class, COUNT(DISTINCT student)\nFROM\n courses\nGROUP BY class\n;\n

\n\nNote: We use

\n`DISTINCT`

here since the student name may duplicated in a class as it is mentioned int he problem description.

| class | COUNT(student) |\n|----------|----------------|\n| Biology | 1 |\n| Computer | 1 |\n| English | 1 |\n| Math | 6 |\n

To continue, we can filter the classes by taking the above query as a sub-query.

\nSELECT\n class\nFROM\n (SELECT\n class, COUNT(DISTINCT student) AS num\n FROM\n courses\n GROUP BY class) AS temp_table\nWHERE\n num >= 5\n;\n

\n\nNote: Make an alias of

\n`COUNT(student)`

(\'num\' in this case) so that you can use in the`WHERE`

clause because it cannot be used directly over there.

`GROUP BY`

and `HAVING`

condition [Accepted]**Algorithm**

Using sub-query is one way to add some condition to a `GROUP BY`

clause, however, using `HAVING`

is another simpler and natural approach. So we can rewrite the above solution as below.

**MySQL**

SELECT\n class\nFROM\n courses\nGROUP BY class\nHAVING COUNT(DISTINCT student) >= 5\n;\n