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.

Some important questions related to the SQL HAVING clause:

What is the purpose of the HAVING clause in SQL?

  • The HAVING clause is used to filter groups of rows returned by the GROUP BY clause based on specified conditions.

  • What happens if you use a column alias defined in the SELECT statement in the HAVING clause?

  • Some database systems allow using column aliases in the HAVING clause if they are defined in the same SELECT statement. However, it's best practice to use the actual column names or aggregate functions.

  • Can you use the HAVING clause without the GROUP BY clause?

  • No, the HAVING clause is typically used in conjunction with the GROUP BY clause. It filters grouped rows based on aggregate conditions.

  • Can you use aggregate functions in the HAVING clause?

  • Yes, the HAVING clause often involves conditions that use aggregate functions such as COUNT(), SUM(), AVG(), etc., to filter groups based on aggregate values.

  • Can you use subqueries in the HAVING clause?

  • Yes, you can use subqueries in the HAVING clause to perform more complex filtering based on aggregated or calculated values.

  • In which order are the clauses executed in a SQL query with the HAVING clause?

  • The order of execution is: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. This means that grouping occurs before selecting and filtering, and after joining tables and applying conditions.

  • Can you use logical operators such as AND, OR, and NOT in the HAVING clause?

  • Yes, you can use logical operators to combine multiple conditions in the HAVING clause to filter groups based on complex criteria.

  • What is the difference between the WHERE and HAVING clauses?

  • The WHERE clause filters individual rows before they are grouped, while the HAVING clause filters groups of rows after they are grouped.

  • How do you filter groups based on aggregate values using the HAVING clause?

  • You can specify conditions in the HAVING clause that involve aggregate functions, such as HAVING COUNT(*) > 10 to filter groups with more than 10 rows.

  • What are some common use cases for the HAVING clause?

  • Common use cases include filtering groups based on aggregate values, finding groups that meet certain criteria, and performing conditional filtering on grouped data.

  • 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:

    
    -- This SQL query counts the number of customers in each country who have a grade assigned to them, 
    -- and displays the country along with the count, but only for countries where the count of grades is greater than 2.
    -- SELECT statement begins
    SELECT 
        cust_country, -- Selects the 'cust_country' column
        COUNT(grade) -- Counts the number of grades for each country
    FROM 
        customer -- Specifies the 'customer' table to retrieve data from
    GROUP BY 
        cust_country -- Groups the result set by the 'cust_country' column
    HAVING 
        COUNT(grade) > 2; -- Filters the grouped results to include only countries where the count of grades is greater than 2
    

    Explanation:

    • This SQL code is a SELECT statement that retrieves data from the 'customer' table.
    • It counts the number of customers in each country who have a grade assigned to them and displays the country along with the count.
    • The SELECT clause selects the 'cust_country' column from the 'customer' table.
    • The COUNT(grade) function counts the number of grades for each country. It is applied within the HAVING clause to filter groups based on this count.
    • The GROUP BY clause groups the result set by the 'cust_country' column, ensuring that calculations are performed for each unique country.
    • The HAVING clause filters the grouped results to include only countries where the count of grades is greater than 2.
    • The query retrieves the country and the count of grades for each country from the 'customer' table, but only for countries where the count of grades is greater than 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
    +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+  
    |CUST_CODE  | CUST_NAME   | CUST_CITY   | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO     | AGENT_CODE |
    +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
    | C00013    | Holmes      | London      | London       | UK           |     2 |     6000.00 |     5000.00 |     7000.00 |       4000.00 | BBBBBBB      | A003       |
    | C00001    | Micheal     | New York    | New York     | USA          |     2 |     3000.00 |     5000.00 |     2000.00 |       6000.00 | CCCCCCC      | A008       |
    | C00020    | Albert      | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     6000.00 |       6000.00 | BBBBSBB      | A008       |
    | C00025    | Ravindran   | Bangalore   | Bangalore    | India        |     2 |     5000.00 |     7000.00 |     4000.00 |       8000.00 | AVAVAVA      | A011       |
    | C00024    | Cook        | London      | London       | UK           |     2 |     4000.00 |     9000.00 |     7000.00 |       6000.00 | FSDDSDF      | A006       |
    | C00015    | Stuart      | London      | London       | UK           |     1 |     6000.00 |     8000.00 |     3000.00 |      11000.00 | GFSGERS      | A003       |
    | C00002    | Bolt        | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | DDNRDRH      | A008       |
    | C00018    | Fleming     | Brisban     | Brisban      | Australia    |     2 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | NHBGVFC      | A005       |
    | C00021    | Jacks       | Brisban     | Brisban      | Australia    |     1 |     7000.00 |     7000.00 |     7000.00 |       7000.00 | WERTGDF      | A005       |
    | C00019    | Yearannaidu | Chennai     | Chennai      | India        |     1 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | ZZZZBFV      | A010       |
    | C00005    | Sasikant    | Mumbai      | Mumbai       | India        |     1 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | 147-25896312 | A002       |
    | C00007    | Ramanathan  | Chennai     | Chennai      | India        |     1 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | GHRDWSD      | A010       |
    | C00022    | Avinash     | Mumbai      | Mumbai       | India        |     2 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | 113-12345678 | A002       |
    | C00004    | Winston     | Brisban     | Brisban      | Australia    |     1 |     5000.00 |     8000.00 |     7000.00 |       6000.00 | AAAAAAA      | A005       |
    | C00023    | Karl        | London      | London       | UK           |     0 |     4000.00 |     6000.00 |     7000.00 |       3000.00 | AAAABAA      | A006       |
    | C00006    | Shilton     | Torento     | Torento      | Canada       |     1 |    10000.00 |     7000.00 |     6000.00 |      11000.00 | DDDDDDD      | A004       |
    | C00010    | Charles     | Hampshair   | Hampshair    | UK           |     3 |     6000.00 |     4000.00 |     5000.00 |       5000.00 | MMMMMMM      | A009       |
    | C00017    | Srinivas    | Bangalore   | Bangalore    | India        |     2 |     8000.00 |     4000.00 |     3000.00 |       9000.00 | AAAAAAB      | A007       |
    | C00012    | Steven      | San Jose    | San Jose     | USA          |     1 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | KRFYGJK      | A012       |
    | C00008    | Karolina    | Torento     | Torento      | Canada       |     1 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | HJKORED      | A004       |
    | C00003    | Martin      | Torento     | Torento      | Canada       |     2 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | MJYURFD      | A004       |
    | C00009    | Ramesh      | Mumbai      | Mumbai       | India        |     3 |     8000.00 |     7000.00 |     3000.00 |      12000.00 | Phone No     | A002       |
    | C00014    | Rangarappa  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | AAAATGF      | A001       |
    | C00016    | Venkatpati  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | JRTVFDD      | A007       |
    | C00011    | Sundariya   | Chennai     | Chennai      | India        |     3 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | PPHGRTS      | A010       |
    +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
    

    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:

    
    -- This SQL query calculates various financial metrics for customers in each city who have a grade of 2, 
    -- and displays the city along with the sum of opening amounts, average receive amount, and maximum payment amount. 
    -- Additionally, it filters the results to include only cities where the average receive amount is greater than 500.
    -- SELECT statement begins
    SELECT 
        cust_city, -- Selects the 'cust_city' column
        SUM(opening_amt), -- Calculates the sum of opening amounts for each city
        AVG(receive_amt), -- Calculates the average receive amount for each city
        MAX(payment_amt) -- Finds the maximum payment amount for each city
    FROM 
        customer -- Specifies the 'customer' table to retrieve data from
    WHERE 
        grade = 2 -- Filters the rows where the grade is equal to 2
    GROUP BY 
        cust_city -- Groups the result set by the 'cust_city' column
    HAVING 
        AVG(receive_amt) > 500; -- Filters the grouped results to include only cities where the average receive amount is greater than 500
    

    Explanation:

    • This SQL code is a SELECT statement that retrieves data from the 'customer' table.
    • It calculates various financial metrics for customers in each city who have a grade of 2 and displays the city along with the sum of opening amounts, average receive amount, and maximum payment amount.
    • The SELECT clause selects the 'cust_city' column from the 'customer' table.
    • SUM(opening_amt) calculates the sum of opening amounts for each city, AVG(receive_amt) calculates the average receive amount, and MAX(payment_amt) finds the maximum payment amount for each city.
    • The WHERE clause filters the rows where the grade is equal to 2, ensuring that only customers with a grade of 2 are considered.
    • The GROUP BY clause groups the result set by the 'cust_city' column, ensuring that calculations are performed for each unique city.
    • The HAVING clause filters the grouped results to include only cities where the average receive amount is greater than 500.
    • The query retrieves the city along with the financial metrics for each city from the 'customer' table, but only for cities where the average receive amount is greater than 500 and the customers have a grade of 2.

    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
    +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+  
    |CUST_CODE  | CUST_NAME   | CUST_CITY   | WORKING_AREA | CUST_COUNTRY | GRADE | OPENING_AMT | RECEIVE_AMT | PAYMENT_AMT |OUTSTANDING_AMT| PHONE_NO     | AGENT_CODE |
    +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
    | C00013    | Holmes      | London      | London       | UK           |     2 |     6000.00 |     5000.00 |     7000.00 |       4000.00 | BBBBBBB      | A003       |
    | C00001    | Micheal     | New York    | New York     | USA          |     2 |     3000.00 |     5000.00 |     2000.00 |       6000.00 | CCCCCCC      | A008       |
    | C00020    | Albert      | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     6000.00 |       6000.00 | BBBBSBB      | A008       |
    | C00025    | Ravindran   | Bangalore   | Bangalore    | India        |     2 |     5000.00 |     7000.00 |     4000.00 |       8000.00 | AVAVAVA      | A011       |
    | C00024    | Cook        | London      | London       | UK           |     2 |     4000.00 |     9000.00 |     7000.00 |       6000.00 | FSDDSDF      | A006       |
    | C00015    | Stuart      | London      | London       | UK           |     1 |     6000.00 |     8000.00 |     3000.00 |      11000.00 | GFSGERS      | A003       |
    | C00002    | Bolt        | New York    | New York     | USA          |     3 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | DDNRDRH      | A008       |
    | C00018    | Fleming     | Brisban     | Brisban      | Australia    |     2 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | NHBGVFC      | A005       |
    | C00021    | Jacks       | Brisban     | Brisban      | Australia    |     1 |     7000.00 |     7000.00 |     7000.00 |       7000.00 | WERTGDF      | A005       |
    | C00019    | Yearannaidu | Chennai     | Chennai      | India        |     1 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | ZZZZBFV      | A010       |
    | C00005    | Sasikant    | Mumbai      | Mumbai       | India        |     1 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | 147-25896312 | A002       |
    | C00007    | Ramanathan  | Chennai     | Chennai      | India        |     1 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | GHRDWSD      | A010       |
    | C00022    | Avinash     | Mumbai      | Mumbai       | India        |     2 |     7000.00 |    11000.00 |     9000.00 |       9000.00 | 113-12345678 | A002       |
    | C00004    | Winston     | Brisban     | Brisban      | Australia    |     1 |     5000.00 |     8000.00 |     7000.00 |       6000.00 | AAAAAAA      | A005       |
    | C00023    | Karl        | London      | London       | UK           |     0 |     4000.00 |     6000.00 |     7000.00 |       3000.00 | AAAABAA      | A006       |
    | C00006    | Shilton     | Torento     | Torento      | Canada       |     1 |    10000.00 |     7000.00 |     6000.00 |      11000.00 | DDDDDDD      | A004       |
    | C00010    | Charles     | Hampshair   | Hampshair    | UK           |     3 |     6000.00 |     4000.00 |     5000.00 |       5000.00 | MMMMMMM      | A009       |
    | C00017    | Srinivas    | Bangalore   | Bangalore    | India        |     2 |     8000.00 |     4000.00 |     3000.00 |       9000.00 | AAAAAAB      | A007       |
    | C00012    | Steven      | San Jose    | San Jose     | USA          |     1 |     5000.00 |     7000.00 |     9000.00 |       3000.00 | KRFYGJK      | A012       |
    | C00008    | Karolina    | Torento     | Torento      | Canada       |     1 |     7000.00 |     7000.00 |     9000.00 |       5000.00 | HJKORED      | A004       |
    | C00003    | Martin      | Torento     | Torento      | Canada       |     2 |     8000.00 |     7000.00 |     7000.00 |       8000.00 | MJYURFD      | A004       |
    | C00009    | Ramesh      | Mumbai      | Mumbai       | India        |     3 |     8000.00 |     7000.00 |     3000.00 |      12000.00 | Phone No     | A002       |
    | C00014    | Rangarappa  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | AAAATGF      | A001       |
    | C00016    | Venkatpati  | Bangalore   | Bangalore    | India        |     2 |     8000.00 |    11000.00 |     7000.00 |      12000.00 | JRTVFDD      | A007       |
    | C00011    | Sundariya   | Chennai     | Chennai      | India        |     3 |     7000.00 |    11000.00 |     7000.00 |      11000.00 | PPHGRTS      | A010       |
    +-----------+-------------+-------------+--------------+--------------+-------+-------------+-------------+-------------+---------------+--------------+------------+
    

    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:

    
    -- This SQL query calculates various financial metrics for customers in each city who have a grade of 2, 
    -- and displays the city along with the sum of opening amounts, average receive amount, and maximum payment amount. 
    -- Additionally, it filters the results to include only cities where the average receive amount is greater than 500, 
    -- and orders the result set by the sum of opening amounts in ascending order.
    -- SELECT statement begins
    SELECT 
        cust_city, -- Selects the 'cust_city' column
        SUM(opening_amt), -- Calculates the sum of opening amounts for each city
        AVG(receive_amt), -- Calculates the average receive amount for each city
        MAX(payment_amt) -- Finds the maximum payment amount for each city
    FROM 
        customer -- Specifies the 'customer' table to retrieve data from
    WHERE 
        grade = 2 -- Filters the rows where the grade is equal to 2
    GROUP BY 
        cust_city -- Groups the result set by the 'cust_city' column
    HAVING 
        AVG(receive_amt) > 500 -- Filters the grouped results to include only cities where the average receive amount is greater than 500
    ORDER BY 
        SUM(opening_amt); -- Orders the result set by the sum of opening amounts in ascending order
    

    Explanation:

    • This SQL code is a SELECT statement that retrieves data from the 'customer' table.
    • It calculates various financial metrics for customers in each city who have a grade of 2 and displays the city along with the sum of opening amounts, average receive amount, and maximum payment amount.
    • The SELECT clause selects the 'cust_city' column from the 'customer' table.
    • SUM(opening_amt) calculates the sum of opening amounts for each city, AVG(receive_amt) calculates the average receive amount, and MAX(payment_amt) finds the maximum payment amount for each city.
    • The WHERE clause filters the rows where the grade is equal to 2, ensuring that only customers with a grade of 2 are considered.
    • The GROUP BY clause groups the result set by the 'cust_city' column, ensuring that calculations are performed for each unique city.
    • The HAVING clause filters the grouped results to include only cities where the average receive amount is greater than 500.
    • The ORDER BY clause orders the result set by the sum of opening amounts in ascending order.

    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

    

    Become a Patron!

    Follow us on Facebook and Twitter for latest update.

    It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

    https://www.w3resource.com/sql/having-clause/sql-having-clause.php