MySQL GROUP BY HAVING clause
MySQL GROUP BY HAVING clause
In this tutorial, we will learn MySQL GROUP BY HAVING clause. We can filter the aggregate information using the HAVING clause.
HAVING clause
The HAVING modifier can be used to require that the groups produced by a GROUP BY clause satisfy particular condition.The HAVING condition can refer to aggregate functions. The HAVING clause is applied after GROUP BY and allows us to filter on the aggregate data that is not available to the WHERE clause.
Syntax
The general syntax for the HAVING clause is as follows:
mysql> SELECT column_list, aggregate_function(column)
FROM <table_name>
GROUP BY grouping_column
HAVING <aggregate_condition> ;
Example
In this example, we will use the Continent table from the world MySQL database.
The following SQL query results in a list of the continents whose average of country population
is greater than 20,000,000.
We will the AVG() aggregate function to compute the average population.
mysql> SELECT Continent, AVG(population)
->FROM Country
->GROUP BY Continent
->HAVING AVG(Population) > 20000000;
+—————+—————–+
| Continent | AVG(population) |
+—————+—————–+
| Asia | 72647562.7451 |
| South America | 24698571.4286 |
+—————+—————–+
2 rows in set (0.00 sec)
HAVING vs WHERE
The WHERE clause is used to filter rows based on single row data. HAVING clause is used to filter based on aggregate data with GROUP BY clause.
The HAVING clause is evaluated after the grouping implied by the GROUP BY clause.
| WHERE Clause | HAVING Clause | |
|---|---|---|
| Usage | Used before grouping | Used after grouping |
| Applies To | Individual rows | Groups of rows |
| Used With | SELECT, UPDATE, DELETE | SELECT with GROUP BY |
| Aggregate Functions | Cannot be used | Can be used |
| Execution Order | Executed first | Executed after WHERE and GROUP BY |
MySQL Tutorials
MySQL Tutorials on this website:
