Db2 HAVING

Summary: in this tutorial, you will learn how to use the Db2 HAVING clause to specify a search condition for groups.

Introduction to Db2 HAVING clause

When you use the SELECT statement to query data from one or more tables, you get a result set that contains all rows of the related tables. To specify a search condition for rows, you use the conditions in the WHERE clause.

Similarly, to specify a search condition for the groups of rows returned by the GROUP BY clause, you use the HAVING clause. The following illustrates the syntax of the HAVING clause:

SELECT
    select_list
FROM
    table_name
GROUP BY
    column1,
    column2,
    ...
HAVING
    search_condition;    

The search_condition consists of one or more Boolean expressions that evaluate to true, false, or unknown. The statement returns only groups that satisfy the search_condition. In other words, it returns only groups that cause the search_condition to evaluate to true.

Db2 HAVING clause examples

We will use the books and publishers tables from the sample database to demonstrate the HAVING clause.

1) Using Db2 HAVING clause to filter groups example

This statement finds publishers that have more than 30 books:

SELECT 
    p.name  publisher, 
    COUNT(*) book_count
FROM 
    books b
    INNER JOIN publishers p
        ON p.publisher_id = b.publisher_id 
GROUP BY 
    p.name
HAVING 
    COUNT(*) > 30
ORDER BY
    book_count;

In this example:

• The GROUP BY and COUNT(*) function returns publishers with their corresponding book counts
• The HAVING clause evaluates each group (publisher) and includes only the publishers that have more than 30 books.

2) Using Db2 HAVING clause to find duplicate rows

First, create a new table named t1 for the demonstration.

CREATE TABLE t1
(
    id INT NOT NULL PRIMARY KEY, 
    c1 CHAR(1) NOT NULL
);

Second, insert some rows into the t1 table.

INSERT INTO t1(id,c1) 
VALUES(1,'A'),(2,'B'),(3,'C'),(4,'C'),(5,'A');

Third, query data from the t1 table.

SELECT 
    id, 
    c1
FROM 
    t1;

ID          C1 
----------- -- 
1           A  
2           B  
3           C  
4           C  
5           A

As you can see, the c1 column has some duplicate values e.g., A and C. Finding these duplicate rows in a table with many rows is not easy.

Fortunately, you can use the HAVING clause to find these duplicate values quickly:

• First, group the values in the column from which you want to find duplicates using the GROUP BY clause.
• Second, use the COUNT() function to get the number of values for each group.
• Third, use the HAVING clause to filter values whose the number of occurrences is greater than one.

Here is the query:

SELECT 
    c1, 
    COUNT(c1) value_count
FROM 
    t1
GROUP BY 
    c1
HAVING 
    COUNT(c1) > 1;

The following shows the output:

C1 VALUE_COUNT 
-- ----------- 
A  2           
C  2

In this tutorial, you have learned how to use the Db2 HAVING clause to specify a search condition for groups.