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.00Code 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:

The most commonly used aggregate functions

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