## 596. Classes More Than 5 Students

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\n

## Solution

\n
\n

#### Approach: Using `GROUP BY` clause and sub-query [Accepted]

\n

Intuition

\n

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

\n

Algorithm

\n

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.

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

Note: We use `DISTINCT` here since the student name may duplicated in a class as it is mentioned int he problem description.

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

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

\n
`SELECT\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
\n

Note: Make an alias of `COUNT(student)` (\'num\' in this case) so that you can use in the `WHERE` clause because it cannot be used directly over there.

\n
\n

#### Approach: Using `GROUP BY` and `HAVING` condition [Accepted]

\n

Algorithm

\n

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.

\n

MySQL

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