The SQL HAVING Clause

The SQL HAVING clause is similar to the WHERE clause; both are used to filter rows in a table based on specified criteria. However, the HAVING clause is used to filter grouped rows instead of single rows. These rows are grouped together by the GROUP BY clause, so, the HAVING clause must always be followed by the GROUP BY clause.

Moreover, the HAVING clause can be used with aggregate functions such as COUNT(), SUM(), AVG(), etc., whereas the WHERE clause cannot be used with them.

Syntax

Following is the basic syntax of the SQL HAVING clause −

SELECT column1, column2, aggregate_function(column)
FROM table_name
GROUP BY column1, column2
HAVING condition;

The following code block shows the position of the HAVING Clause in a query −

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

HAVING with GROUP BY Clause

We can use the HAVING clause with the GROUP BY clause to filter groups of rows that meet certain conditions. It is used to apply a filter to the result set after the aggregation has been performed.

Example

Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary, using the following query −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', 23, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00),
(7, ''Muffy'', 24, ''Indore'', 10000.00);

The table created is as shown below −

Now, we are grouping the records of the CUSTOMERS table based on the columns ADDRESS and AGE and filtering the groups where the AGE value is less than 25.

SELECT ADDRESS, AGE, MIN(SALARY) AS MIN_SUM 
FROM CUSTOMERS 
GROUP BY ADDRESS, AGE HAVING AGE > 25;

Output

The result produced is as follows −

HAVING with ORDER BY Clause

The ORDER BY clause is used to arrange/sort the records of the result of a SELECT query based on a specific column (either in ascending order or in descending order). If we use the ORDER BY clause with the HAVING clause we can sort the filtered groups in the desired order.

Example

Following query groups the records of the CUSTOMERS table based on the columns AGE and ADDRESS, filters the groups where the SALARY value is less than 5000 and, arranges the remaining groups in descending order based the total salaries of each group.

SELECT ADDRESS, AGE, SUM(SALARY) AS TOTAL_SALARY 
FROM CUSTOMERS 
GROUP BY ADDRESS, AGE HAVING TOTAL_SALARY >=5000 
ORDER BY TOTAL_SALARY DESC;

Output

The result produced is as follows −

HAVING Clause with COUNT() Function

The HAVING clause can be used with the COUNT() function to filter groups based on the number of rows they contain.

Example

Following query groups the records of the CUSTOMERS table based on the AGE column and, retrieves the details of the group that has more than two entities −

SELECT AGE, COUNT(AGE) 
FROM CUSTOMERS GROUP BY AGE HAVING COUNT(age) > 2;

Output

This would produce the following result −

Query OK, 0 rows affected (0.02 sec)

HAVING Clause with AVG() Function

The HAVING clause can also be used with the AVG() function to filter groups based on the average value of a specified column.

Example

Now, we are retrieving the city of the customers whose average salary is greater than 5240 −

SELECT ADDRESS, AVG(SALARY) as AVG_SALARY 
FROM CUSTOMERS 
GROUP BY ADDRESS HAVING AVG(SALARY) > 5240;

Output

Following is the output of the above query −

HAVING Clause with MAX() Function

We can also use the HAVING clause with MAX() function to filter groups based on the maximum value of a specified column.

Example

Now, we are retrieving the city of the customers whose maximum salary is greater than 5240 −

SELECT ADDRESS, MAX(SALARY) as MAX_SALARY 
FROM CUSTOMERS 
GROUP BY ADDRESS HAVING MAX(SALARY) > 5240;

Output

The result obtained is as follows −