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:

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