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.

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:

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:

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