SQL HAVING clause
Having Clause
SQL HAVING clause specifies a search condition for a group or an aggregate. HAVING is usually used in a GROUP BY clause, but even if you are not using GROUP BY clause, you can use HAVING to function like a WHERE clause. You must use HAVING with SQL SELECT.
Syntax:
SELECT <column_list> FROM < table name > WHERE <search_condition]> GROUP BY <columns> [HAVING] <search_condition]> [ORDER BY {order_expression [ASC | DESC]}[, ...]];
Parameters:
Name | Description |
---|---|
table_name | Name of the table. |
column_list | Name of the columns of the table. |
columns | Name of the columns which will participate in grouping. |
How a HAVING clause works IN SQL?
- The select clause specifies the columns.
- The from clause supplies a set of potential rows for the result.
- The where clause gives a filter for these potential rows.
- The group by clause divide the rows in a table into smaller groups.
- The having clause gives a filter for these group rows.
Example: SQL HAVING clause
The following query display cust_country and number of customers for the same grade for each cust_country, with the following condition -
1. number of customer for a same 'grade' must be more than 2,
Sample table: customer
SQL Code:
SELECT cust_country,COUNT(grade)
FROM customer
GROUP BY cust_country
HAVING COUNT(grade)>2;
Relational Algebra Expression:

Relational Algebra Tree:

Sample Output:
CUST_COUNTRY COUNT(GRADE) -------------------- ------------ USA 4 India 10 Australia 3 Canada 3 UK 5
Pictorial presentation:

SQL HAVING using where
In the following example, the SQL WHERE clause along with the HAVING clause have used to make a select statement.
Example:
Sample table: customer
To get list of cust_city, sum of opening_amt, average of receive_amt and maximum payment_amt from customer table with following conditions-
1. grade of customer table must be 2,
2. average of receive_amt for each group of cust_city must be more than 500,
then, the following SQL statement can be used:
SQL Code:
SELECT cust_city,SUM(opening_amt),
AVG(receive_amt),MAX(payment_amt)
FROM customer
WHERE grade=2
GROUP BY cust_city
HAVING AVG(receive_amt)>500;
Sample Output:
CUST_CITY SUM(OPENING_AMT) AVG(RECEIVE_AMT) MAX(PAYMENT_AMT) ----------------------------------- ---------------- ---------------- ---------------- Bangalore 29000 8250 7000 Brisban 7000 7000 9000 London 10000 7000 7000 Mumbai 7000 11000 9000 New York 3000 5000 2000 Torento 8000 7000 7000
SQL HAVING with order by
In the following example, the SQL WHERE clause along with the HAVING clause is used to make a query. An ORDER BY clause arranges the final result in the specific order. The default order is ascending.
Example:
Sample table : customer
To get list of cust_city, sum of opening_amt, average of receive_amt and maximum payment_amt from customer table with following conditions-
1. grade of customer table must be 2,
2. average of receive_amt for each group of cust_city must be more than 500,
3. the output should be arranged in the ascending order of SUM(opening_amt),
then, the following SQL statement can be used:
SQL Code:
SELECT cust_city,SUM(opening_amt),
AVG(receive_amt),MAX(payment_amt)
FROM customer
WHERE grade=2
GROUP BY cust_city
HAVING AVG(receive_amt)>500
ORDER BY SUM(opening_amt);
Sample Output:
CUST_CITY SUM(OPENING_AMT) AVG(RECEIVE_AMT) MAX(PAYMENT_AMT) ----------------------------------- ---------------- ---------------- ---------------- New York 3000 5000 2000 Brisban 7000 7000 9000 Mumbai 7000 11000 9000 Torento 8000 7000 7000 London 10000 7000 7000 Bangalore 29000 8250 7000
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.
Previous: Group By
Next: SELECT with DISTINCT
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join