HAVING clause is used to apply a filter on the result of  GROUP BY based on the specified condition. The conditions are Boolean type i.e. use of logical operators(AND, OR). This clause was included in SQL as the WHERE keyword failed when we use it with aggregate expressions. It is a very generally used clause in SQL. Similar to WHERE it helps to apply conditions, but HAVING works with groups. If you wish to filter a group, the HAVING clause comes into action.

Few important points:

• It is used to filter data according to the conditions provided.
• It is generally used in reports of large data.  
• It is only used with the SELECT clause.
• The expression in the syntax can only have constants.
• In the query, ORDER BY  is to be placed after the HAVING clause, if any.
• It implements in column operation.
• It is generally used after GROUP BY.
• The GROUP BY clause is used to arrange required data into groups.

HAVING Clause Syntax

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
HAVING condition;

Expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause near the end of the SQL statement.

aggregate_function

This is an aggregate function such as the SUM, COUNT, MIN, MAX, or AVG functions.

aggregate_expression

This is the column or expression that the aggregate_function will be used on.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. These are the conditions for the records to be selected.

HAVING condition

This is a further condition applied only to the aggregated results to restrict the groups of returned rows. Only those groups whose condition evaluates to TRUE will be included in the result set.

Difference between HAVING Clause and WHERE Clause

The difference between the WHERE and HAVING clauses in the database is the most important question asked during an IT interview. The following table shows the comparisons between these two clauses, but the main difference is that the WHERE clause uses conditions for filtering records before any groupings are made while HAVING clause uses conditions for filtering values from a group.

Examples using HAVING clause

ORDER_DETAILS
| ID | EMPLOYEE | DEPT | CITY    | SALARY  | SALES
| +——+----------+-----+-----------+----------+----- 
| 1   | Satvi   | HR   | Atlanta | 9000.00 | 90000
| 2   | Kannav  | MKT  | BBSR    | 5000.00 | 50000
| 3   | Kiran   | MKT  | BBSR    | 8000.00 | 50000
| 4   | Lilly   | IT   | WH      | 6000.00 | 40000
| 5   | Alex    | IT   | SFO     | 8500.00 | 80000
| 6   | Stacy   | PR   | Malibu  | 4500.00 | 20000
| 7   | Barbie  | PR   | Malibu  | 1000.00 | 25000

Example – Using SUM function

You could also use the SQL SUM function to return the name of the department and the total sales (in the associated department). This clause will filter the results so that only departments with sales greater than $50000 will be returned.

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 50000;



Result
DEPT                  Total sales
HR                          90000
MKT                        100000
IT                         120000 

Example – Using COUNT function

You could use the SQL COUNT function to return the name of the department and the number of employees (in the associated department) that makeovers or equals $25,000 / year. This clause will filter the results so that only departments with more than 1 employee will be returned.

SELECT department, COUNT(*) AS "Number of employees"
FROM employees
WHERE salary >= 25000
GROUP BY department
HAVING COUNT(*) > 1;




Result
DEPT          Number of employees
MKT                             2
IT                              2
PR                              1

Example – Using MIN function

You could also use the SQL MIN function to return the name of each department and the minimum salary in the department. This clause will return only those departments where the minimum salary is greater than $35,000.

SELECT department, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) > 35000;




Result
DEPT                Lowest salary
HR                          90000
MKT                         50000
IT                          40000

Example – Using MAX function

For example, you could also use the SQL MAX function to return the name of each department and the maximum salary in the department. This clause will return only those departments whose maximum salary is less than $60,000.

SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) < 60000;



Result
DEPT               Highest salary
MKT                         50000
PR                          25000

Example of HAVING Clause with JOIN

“Orders” table:

“Employees” table:

Employees that have registered more than or equals to 3 orders

SELECT Employees.LastName, 
       COUNT(Orders.OrderID) AS NumberOfOrders
FROM (Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) >= 3;

Employees with last name’Aube’or ‘Kong’ that have registered more than 2 orders

SELECT Employees.LastName, 
      COUNT(Orders.OrderID) AS NumberOfOrders
FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Aube' OR LastName = 'Kong'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 2;

Read DB2 blogs: Click Here IBM DB2 Manual: Click Here