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
Practice SQL Exercises
- SQL Exercises, Practice, Solution
- SQL Retrieve data from tables [33 Exercises]
- SQL Boolean and Relational operators [12 Exercises]
- SQL Wildcard and Special operators [22 Exercises]
- SQL Aggregate Functions [25 Exercises]
- SQL Formatting query output [10 Exercises]
- SQL Quering on Multiple Tables [8 Exercises]
- FILTERING and SORTING on HR Database [38 Exercises]
- SQL JOINS
- SQL SUBQUERIES
- SQL Union[9 Exercises]
- SQL View[16 Exercises]
- SQL User Account Management [16 Exercise]
- Movie Database
- BASIC queries on movie Database [10 Exercises]
- SUBQUERIES on movie Database [16 Exercises]
- JOINS on movie Database [24 Exercises]
- Soccer Database
- Introduction
- BASIC queries on soccer Database [29 Exercises]
- SUBQUERIES on soccer Database [33 Exercises]
- Hospital Database
- Employee Database
- More to come!
Want to improve the above article? Contribute your Notes/Comments/Examples through Disqus.
Previous: Group By
Next: SELECT with DISTINCT
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework