We use HAVING Clause to restrict the groups of rows returned, to the rows which satisfy the condition and the GROUP BY Clause. We employ the WHERE Clause to mention the condition for refining the rows returned by the SELECT statement. However, we cannot use this Clause for the rows returned by the GROUP BY Clause.

To mention the search condition for refining the grouped rows generated by the GROUP BY Clause.

The Syntax is as follows:

Select column_list from table_name HAVING condition;

The HAVING clause is mentioned right after the group by Clause.
To complete the HAVING clause, we need to specify the condition with one or more Boolean expressions.
The condition evaluates the group of rows returned by the SELECT statement and returns the rows that satisfy the condition.

We can also use aggregate functions with the HAVING Clause similarly to the GROUP BY Clause.

The Syntax is as follows:

SELECT column_list aggregate_function(exp) FROM tabl_name [WHERE_condition] GROUP BY col_1, col_2 HAVING condition;

Aggregate functions can be one of these- SUM(), COUNT(), MIN(), MAX() or AVG().

Let's see examples

We will be using the table named 'Country'. Which consists of three columns, namely state, city, and population

MariaDB [student]> select * from country;

Using SUM() function with HAVING Clause

In the following query, we will be using the SUM() function to calculate the total population present in each state and the HAVING Clause

MariaDB [student]> SELECT state, SUM(population) AS 'Population' from country where population>100000 GROUP BY state HAVING SUM(population)>350000;

Output:

In the above example, we have returned the state names and sum of the population. We are calculation the sum of the population of the cities with a population over 100000, and the rows are grouped based on the state.

After grouping the rows, we have imposed one more condition using the HAVING Clause, i.e., the result will contain only the states with a total population of over 3,50,000.Here there are only 3 states with a total population of over 350000.

Using COUNT () function with HAVING Clause

In the following query, we will be using the COUNT () function to calculate the total number of cities present in each state and the HAVING Clause.

MariaDB [student]> select state, COUNT(city) total_cities FROM country GROUP BY state HAVING count(*) >=3;

In the above example, initially, we use count() to count the city in each state using GROUP BY Clause.

After that, the HAVING clause is used with the condition consisting of count() where it will refine the states with cities greater than or equal to 3 will be returned.

Using MIN() function with HAVING Clause

We will be using the HAVING Clause with the MIN() function in MariaDB in the following query.

MariaDB [student]> select state,min(population) lowest_population from country GROUP BY state HAVING MIN(population) < 200000;

In the above example,

Initially, the cities are grouped based on the states they belong to.
Next, from all the cities present in the particular state, we have calculated the minimum value of population using the min() function.
Here we can see the lowest_population column represents the lowest population of the city from a particular state
Finally, we have used the HAVING clause, which restricts the rows of the state with a population greater than 200000.

Using MAX() function with HAVING Clause

We will be using the HAVING Clause with the MAX() function in MariaDB in the following query.

MariaDB [student]> select state,max(population) highest_population from country group by state HAVING MAX(population) > 200000 order by state;