DB2 – SQL HAVING Clause

In addition to being able to group data using GROUP BY, SQL also allows you to filter which groups to
include and which to exclude.

For example, you might want a list of all customers who have made at
least two orders. To obtain this data you must filter based on the complete group, not on individual
rows.

HAVING is very similar to WHERE. The only difference is that WHERE filters rows and HAVING filters
groups. WHERE filters before data is grouped and HAVING filters after data is grouped.

Also, HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

DB2 Database:

Below is a selection from the “Orders” table in the DB2 database.

OrderidCustomernumberOrderdate
1001102020-09-08
1002992020-09-01
1003172020-08-25
1004762020-07-19
1005992020-09-21
1006172020-08-25
1007762020-05-19
1008992020-03-01
1009302020-06-25
1010402020-09-19
1011762020-05-19
1011992020-05-19

Example 1:

The following SQL statement selects the list of all customernumer who have made at least two orders.

SELECT Customernumber, COUNT(*) AS Orders
   FROM Orders
   GROUP BY Customernumber
   HAVING COUNT(Customernumber) >= 2;

In this statement, GROUP BY clause is used to group the data by Customernumber so that the COUNT(*)
function can return the number of orders placed by each customernumer.

The HAVING clause filters the data so that only
orders with two or more items is returned.

Result:

CustomernumberOrders
172
763
994

Example 2: Grouping and Sorting

To sort the output of GROUP BY, you need to use ORDER BY.

SELECT Customernumber, COUNT(*) AS Orders
    FROM Orders
    GROUP BY Customernumber
    HAVING COUNT(Customernumber) >= 2;
     ORDER BY COUNT(Customernumber) DESC;

In this statement, GROUP BY clause is used to group the data by Customernumber so that the COUNT(*)
function can return the number of orders placed by each customernumer.

The HAVING clause filters the data so that only
orders with two or more items is returned. Finally, the output is sorted using the ORDER BY clause.

Result:

CustomernumberOrders
994
763
172

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!

Are you looking for Job Change? Job Portal