w3resource

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 Expression: SQL HAVING clause.

Relational Algebra Tree:

Relational Algebra Tree: SQL HAVING clause.

Sample Output:

CUST_COUNTRY         COUNT(GRADE)
-------------------- ------------
USA                             4
India                          10
Australia                       3
Canada                          3
UK                              5

Pictorial presentation:

Example: SQL HAVING clause

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



Follow us on Facebook and Twitter for latest update.