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.
GROUP BY clause and sub-query [Accepted]Intuition
\nFirst, we can count the student number in each class. And then select the ones have more than 5 students.
\nAlgorithm
\nTo 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
\nDISTINCThere 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
\nCOUNT(student)(\'num\' in this case) so that you can use in theWHEREclause because it cannot be used directly over there.
GROUP BY and HAVING condition [Accepted]Algorithm
\nUsing 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
\nSELECT\n class\nFROM\n courses\nGROUP BY class\nHAVING COUNT(DISTINCT student) >= 5\n;\n