GROUP BY HAVING Clause
The GROUP BY clause is used when more than one aggregate functions are given in the SELECT statement.
Query results are formed into a group. By using the HAVING clause, you can set the condition so that only groups satisfying the specified condition will be retrieved instead of all groups created by the GROUP BY clause.
GROUP BY expression_list [ HAVING search_condition]
- expression_list : The expression_list consists of one or more column names or expressions. Each field must be separated by a comma (,).
- search_condition : The query result groups can be limited by search_condition specified in the HAVING option. Even when multiple groups are created as the result of the query, you can set the condition so that only groups satisfying a specific logical expression will be displayed.
- Example 1
- The following is an example of retrieving the number of gold medals in each Olympic Games since 1988. In the participant table, the sum of gold is calculated in each host_year. In this example, the participant table's instance has 5 different host_year values. Therefore, 5 groups are created, and for each host_year value group, sum(gold) value is calculated.
SELECT host_year, SUM(gold) FROM participant WHERE host_year >= 1988 GROUP BY host_year;
=== <Result of SELECT Command in Line 2> ===
5 rows selected.
- Example 2
- The following is an example of grouping the athletes that participated in the Olympic Games by the nation and retrieving the nation codes and the number of athletes of countries with more than 200 participants. You can retrieve the instances satisfying the specified conditions by using the GROUP BY clause in combination with the HAVING clause. In the example above, only instances whose count(code) is greater than 200 are retrieved, grouping by nation_code. Whereas WHERE is the conditional clause of SELECT, HAVING is the conditional clause of GROUP BY.
- In a GROUP BY clause, expressions as well as columns can be listed. In this case, the expressions can be composed of columns, functions and constant values.
SELECT nation_code, COUNT(code) FROM athlete GROUP BY nation_code HAVING COUNT(code) > 200;
=== <Result of SELECT Command in Line 2> ==">
10 rows selected.