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 | +---------+------------+
+---------+ | class | +---------+ | Math | +---------+
The students should not be counted duplicate in each course.
GROUP BYclause and sub-query [Accepted]
First, we can count the student number in each class. And then select the ones have more than 5 students.\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.
SELECT\n class, COUNT(DISTINCT student)\nFROM\n courses\nGROUP BY class\n;\n
Note: We use\n
DISTINCThere 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.\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
Note: Make an alias of\n
COUNT(student)(\'num\' in this case) so that you can use in the
WHEREclause because it cannot be used directly over there.
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.
SELECT\n class\nFROM\n courses\nGROUP BY class\nHAVING COUNT(DISTINCT student) >= 5\n;\n