HAVING Clause in DB2 SQL Queries

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.

HAVING	WHERE
This clause is used in database systems to fetch the data/values from the groups according to the given condition.	This clause is used in database systems to fetch the data/values from the tables according to the given condition.
It is always executed with the GROUP BY clause.	It can be executed without the GROUP BY clause.
It can include SQL aggregate functions in a query or statement.	We cannot use the SQL aggregate function with WHERE clause in statements.
We can only use SELECT statement with HAVING clause for filtering the records.	Whereas, we can easily use WHERE clause with UPDATE, DELETE, and SELECT statements.
It is used in SQL queries after the GROUP BY clause.	It is always used before the GROUP BY clause in SQL queries.
We can implements this SQL clause in column operations.	We can implements this SQL clause in row operations.
It is a post-filter.	It is a pre-filter.
It is used to filter groups.	It is used to filter the single record of the table.

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:

OrderID	CustomerID	EmployeeID	OrderDate
101	10	5	1996-07-04
102	11	1	1996-07-05
103	14	4	1996-07-08
104	24	3	1996-07-08
105	16	4	1996-07-09
106	18	3	1996-07-10
107	24	5	1996-07-11
108	18	1	1996-07-12
109	28	3	1996-07-15
110	25	4	1996-07-16
111	20	2	1996-07-17
112	13	4	1996-07-18
113	15	4	1996-07-19

“Employees” table:

EmployeeID	LastName	FirstName	BirthDate
1	Aube	Andrew	1968-12-08
2	Roberts	Wesley	1952-02-19
3	Kong	Ming	1963-08-30
4	Kutz	Margaret	1958-09-19
5	Parsley	Steven	1955-03-04

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;

LastName	NumberOfOrders
Kutz	5
Kong	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;

LastName	NumberOfOrders
Kong	3

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