Db2 Aggregate Functions

Summary: in this tutorial, you will learn about the Db2 aggregate functions and how to use the most common aggregate functions in Db2 to calculate the aggregate of a set of values.

Introduction to Db2 aggregate functions

An aggregate function operates on a set of values and returns a single value. The most commonly used aggregate functions are AVG(), COUNT(), MAX(), MIN(), and SUM().

For example, the AVG() function takes a set of numbers and returns the average of all the numbers.

The aggregate functions are often used with the GROUP BY clause to calculate the aggregate for each group. For example, you can find the highest rated book of every publisher by using the MAX() function with the GROUP BY clause.

ALL vs. DISTINCT

Some aggregate functions accept ALL and DISTINCT options:

  • The DISTINCT option enables the aggregate function to consider only distinct values in the calculation.
  • The ALL option, on the other hand, enables the aggregate function to take all values including duplicates in the calculation.

The aggregate functions will always use ALL by default if you don’t specify ALL or DISTINCT explicitly.

For example, if you use the COUNT() function with DISTINCT option for the set (1,2,2,3), the COUNT() function will return 3. If you don’t specify anything option or use the ALL option, the COUNT() function will return 4.

NULL treatments

Some aggregate functions consider NULL values like COUNT(*) while others ignore them such as AVG(), MIN(), MAX(), and SUM().

Db2 aggregate function examples

We’ll use the books table from the sample database for the demo.

1) Db2 AVG() function example

The AVG() function returns the average of values of a set.

This example uses the AVG() function to return the average rating of all books:

SELECT CAST(AVG(rating) AS DEC(4,2)) FROM books;
Code language: SQL (Structured Query Language) (sql)

The CAST() function is used to convert the result to a decimal with two number after the decimal point.

RESULT ------ 4.00
Code language: SQL (Structured Query Language) (sql)

2) Db2 COUNT() function example

The COUNT() function return the number of values in a set.

This example uses the COUNT() function to find the number of books from the books table:

SELECT COUNT(*) FROM books;
Code language: SQL (Structured Query Language) (sql)

Here is the number of books in the books table:

RESULT ----------- 1091
Code language: SQL (Structured Query Language) (sql)

3) Db2 MAX() function example

The MAX() function returns the highest value in a set.

The following example uses the MAX() function to return the highest rating of all books:

SELECT MAX(rating) result FROM books;
Code language: SQL (Structured Query Language) (sql)

Here is the highest book rating:

RESULT ------ 5.00
Code language: SQL (Structured Query Language) (sql)

4) Db2 MIN() function example

The MIN() function returns the lowest value in a set.

This statement uses the MIN() function to get the lowest rating of all books:

SELECT MIN(rating) result FROM books;
Code language: SQL (Structured Query Language) (sql)

Here is the lowest book rating:

RESULT ------ 2.96
Code language: SQL (Structured Query Language) (sql)

5) Db2 SUM() function example

The SUM() function returns the sum of values of a set.

This example uses the SUM() function to return the total of pages of all books from the books table:

SELECT SUM(total_pages) result FROM books;
Code language: SQL (Structured Query Language) (sql)

Here is the total of pages of all books:

RESULT ----------- 508504
Code language: SQL (Structured Query Language) (sql)

6) Db2 LISTAGG() function example

The LISTAGG() function aggregates multiple strings into a single string by concatenating the strings.

The following example uses the LISTAGG() function to return the books and a comma-separated list of authors of each book.

SELECT b.title, LISTAGG(a.first_name || ' ' || a.last_name,',') author_list FROM books b INNER JOIN book_authors ba ON ba.book_id = b.book_id INNER JOIN authors a ON a.author_id = ba.author_id GROUP BY title;
Code language: SQL (Structured Query Language) (sql)

This picture shows the partial output:

Db2 Aggregate Function - LISTAGG example

The most commonly used aggregate functions

The following table lists the most commonly used aggregate functions in Db2:

FunctionDescription
AVGReturn the average of a set of numbers.
COUNTReturn the number of rows or values in a set.
COUNT_BIGReturn the number of rows or values in a set with the result in a large integer value.
LISTAGGAggregate a set of strings into a single string by concatenating the strings.
MINReturn the minimum value in a set of values.
MAXReturn the maximum value in a set of values.
SUMReturn the sum of all non-null values.
Was this tutorial helpful ?