# 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:

```.wp-block-code {
border: 0;
}

.wp-block-code > div {
overflow: auto;
}

.shcb-language {
border: 0;
clip: rect(1px, 1px, 1px, 1px);
-webkit-clip-path: inset(50%);
clip-path: inset(50%);
height: 1px;
margin: -1px;
overflow: hidden;
position: absolute;
width: 1px;
word-wrap: normal;
word-break: normal;
}

.hljs {
box-sizing: border-box;
}

.hljs.shcb-code-table {
display: table;
width: 100%;
}

.hljs.shcb-code-table > .shcb-loc {
color: inherit;
display: table-row;
width: 100%;
}

.hljs.shcb-code-table .shcb-loc > span {
display: table-cell;
}

.wp-block-code code.hljs:not(.shcb-wrap-lines) {
white-space: pre;
}

.wp-block-code code.hljs.shcb-wrap-lines {
white-space: pre-wrap;
}

.hljs.shcb-line-numbers {
border-spacing: 0;
counter-reset: line;
}

.hljs.shcb-line-numbers > .shcb-loc {
counter-increment: line;
}

.hljs.shcb-line-numbers .shcb-loc > span {
}

.hljs.shcb-line-numbers .shcb-loc::before {
border-right: 1px solid #ddd;
content: counter(line);
display: table-cell;
text-align: right;
-webkit-user-select: none;
-moz-user-select: none;
-ms-user-select: none;
user-select: none;
white-space: nowrap;
width: 1%;
}```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:

## The most commonly used aggregate functions

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