MariaDB: HAVING Clause

This MariaDB tutorial explains how to use the MariaDB HAVING clause with syntax and examples.

Description

The MariaDB HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE.

Syntax

The syntax for the HAVING clause in MariaDB is:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
HAVING condition;

Parameters or Arguments

aggregate_function

It can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.

expression1, expression2, ... expression_n

The expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY clause.

WHERE conditions

Optional. These are the conditions for the records to be selected.

HAVING condition

This is a further condition applied only to the aggregated results to restrict the groups of returned rows. Only those groups whose condition evaluates to TRUE will be included in the result set.

Example - Using SUM function

Let's look at an example of how to use the HAVING clause with the SUM function in MariaDB.

For example:

SELECT site_id, SUM(file_size) AS "Total"
FROM pages
WHERE site_id < 100
GROUP BY site_id
HAVING SUM(file_size) > 500;

This HAVING clause example uses the SUM function to return the site_id and the total of all file_size values (for that site_id) where the site_id is less than 100. The MariaDB HAVING clause will filter the results so that only site_id values with a total file_size greater than 500 will be returned.

Example - Using COUNT function

Let's look at how to use the HAVING clause with the COUNT function in MariaDB.

For example:

SELECT site_id, COUNT(*) AS "Number of pages per site"
FROM pages
WHERE site_name in ('TechOnTheNet.com', 'BigActivities.com')
GROUP BY site_id
HAVING COUNT(*) >= 999;

This HAVING clause example uses the COUNT function in MariaDB to return the site_id and the count of all pages (for that site_id) that have a site_name of either 'TechOnTheNet.com' or 'BigActivities.com'. The MariaDB HAVING clause will filter the results so that only site_id values with greater than or equal to a total page count of 999 will be returned.

Example - Using MIN function

Let's next look at an example of how to use the HAVING clause with the MIN function in MariaDB.

For example:

SELECT site_id, MIN(file_size) AS "Smallest File"
FROM pages
where site_id < 50
GROUP BY site_id
HAVING MIN(file_size) < 150;

This HAVING clause example uses the MIN function in MariaDB to return the site_id and the minimum file_size (for that site_id) where the site_id is less than 50. The HAVING clause will return only those site_id values where the minimum file_size is less than 150.

Example - Using MAX function

Finally, let's look at an example of how to use the HAVING clause in MariaDB with the MAX function.

For example:

SELECT site_id, MAX(file_size) AS "Largest File"
FROM pages
where site_id < 50
GROUP BY site_id
HAVING MAX(file_size) > 2500;

This HAVING clause example in MariaDB uses the MAX function to return the site_id and the minimum file_size (for that site_id) where the site_id is less than 50. The HAVING clause will return only those site_id values where the maximum file_size is greater than 2500.