DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Db2 Basics » Db2 HAVING

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;
db2 having example

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.

  • Was this tutorial helpful ?
  • YesNo
Previous Db2 GROUP BY
Next Db2 Subquery

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.