GROUP BY clause and sub-query [Accepted]
First, we can count the student number in each class. And then select the ones have more than 5 students.
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 class, COUNT(DISTINCT student) FROM courses GROUP BY class ;
Note: We use
DISTINCThere since the student name may duplicated in a class as it is mentioned int he problem description.
| class | COUNT(student) | |----------|----------------| | Biology | 1 | | Computer | 1 | | English | 1 | | Math | 6 |
To continue, we can filter the classes by taking the above query as a sub-query.
SELECT class FROM (SELECT class, COUNT(DISTINCT student) AS num FROM courses GROUP BY class) AS temp_table WHERE num >= 5 ;
Note: Make an alias of
COUNT(student)('num' in this case) so that you can use in the
WHEREclause because it cannot be used directly over there.
GROUP BY and
HAVING condition [Accepted]
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 class FROM courses GROUP BY class HAVING COUNT(DISTINCT student) >= 5 ;