Summary: in this tutorial, you will learn how to use the MariaDB having clause to specify a search condition for groups.
Introduction to MariaDB having clause
The where clause allows you to specify a search condition for filtering rows returned by the select statement. However, it cannot be used for filtering summarized rows created by the group by clause.
To specify a search condition for filtering summarized rows generated by the group by clause, you use the having clause.
The following illustrates the syntax of the having clause:
select select_list from table_name group by column1, column2, ... having search_condition; Code language: SQL (Structured Query Language) (sql)The having clause appears immediately after the group by clause. To form the having clause, you specify a search_condition that may consist of one or more Boolean expressions. The select statement will return only groups (or rows) which cause the search_condition to evaluate to true.
MariaDB having clause examples
We’ll use the countries and regions tables from the nation sample database:
A) Using the MariaDB having clause to filter group example
The following example uses the having clause to find regions that have more than 10 countries:
select regions.name region, count(country_id) country_count from countries inner join regions using (region_id) group by (regions.name) having count(region_id) > 10 order by country_count desc; Code language: SQL (Structured Query Language) (sql)
B) Using the MariaDB having clause with a complex condition example
The following example uses the having clause to find countries that have more than 10 countries and total areas greater than 1,000,000 km2:
select regions.name region, count(country_id) country_count, sum(area) area from countries inner join regions using (region_id) group by (regions.name) having count(region_id) > 10 and area > 1000000 order by area desc, country_count desc; Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use the MariaDB having clause to specify a search condition for groups.
