**Summary**: in this tutorial, you will learn how to use the DB2 `AVG()`

function to calculate the average of a set of non-null values.

## Introduction to Db2 `AVG()`

function

The Db2 `AVG()`

function is an aggregate function that returns the average of a set of values. Here is the syntax of the `AVG()`

function:

`AVG(expression)`

The `AVG()`

function accepts a set of numeric values and returns the average of these numbers.

The data type of the return value depends on the data type of the input numbers. The `AVG()`

function determines the data type of the return value based on the following rules:

- If the argument is
`DECFLOAT(n)`

, the return type is`DECFLOAT(34)`

. - If the argument is
`SMALLINT`

, the return type is`BIGINT`

. - If the argument is single precision floating-point, the return type is double precision floating-point.
- Otherwise, the return type is the same as the type of the argument.

The `AVG()`

function returns `NULL`

if the set is empty.

Note that the `AVG()`

function ignores `NULL`

values in the calculation.

## Db2 `AVG()`

illustration

First, create a new table named `avg_demo`

that contains one integer column:

```
CREATE TABLE avg_demo(c1 INT);
```

Second, insert four rows into the `avg_demo`

table:

```
INSERT INTO avg_demo(c1)
VALUES(1),(2),(3),(NULL);
```

Third, view the data from the `avg_demo`

table by using the following `SELECT`

statement:

```
SELECT c1 FROM avg_demo;
```

Fourth, use the `AVG()`

function to return the average values in the c1 column of the `avg_demo`

table:

```
SELECT
avg(c1)
FROM
avg_demo;
```

Here is the output:

```
AVERAGE
-----------
2
```

Behind the scenes, Db2 performs the following calculation:

- First, ignore the NULL values.
- Then, divide the sum of all values by the number of values (1+2+3) / 3 = 2.

## Db2 `AVG()`

examples

Let’s take some examples of using the `AVG()`

function. We’ll use the `books`

table from the sample database for the demonstration purposes.

### 1) Using DB2 `AVG()`

function to calculate the average of values example

This example uses the `AVG()`

function to calculate the average rating of all books in the `books`

table:

```
SELECT
AVG(rating) avg_book_rating
FROM
books;
```

Here is the output:

```
AVG_BOOK_RATING
-------------------------------
4.00466544454628780934922089825
```

To make the result more usable, you can use the `CAST`

expression as follows:

```
SELECT
CAST(AVG(rating) AS DEC(4,2)) avg_book_rating
FROM
books;
```

Now the output is much much more readable:

```
AVG_BOOK_RATING
---------------
4.00
```

### 2) Using DB2 `AVG()`

function with the `GROUP BY`

clause example

The `AVG()`

function is often used with the `GROUP BY`

clause to find the average values of groups.

The following example returns the average rating of books by publishers:

```
SELECT
p.name publisher,
CAST(AVG(b.rating) AS DEC(4,2)) avg_rating
FROM
books b
INNER JOIN publishers p
ON p.publisher_id = b.publisher_id
GROUP BY
p.name
ORDER BY avg_rating DESC;
```

This picture shows the partial output:

In this example, the `GROUP BY`

clause groups rows by publishers and the `AVG()`

function returns the average book rating for every group.

### 3) Using DB2 `AVG()`

function with the `HAVING`

clause example

This example returns the publishers whose average book ratings are higher than `4.5`

:

```
SELECT
p.name publisher,
CAST(AVG(b.rating) AS DEC(4,2)) avg_rating
FROM
books b
INNER JOIN publishers p
ON p.publisher_id = b.publisher_id
GROUP BY
p.name
HAVING
AVG(b.rating) > 4.5
ORDER BY avg_rating DESC;
```

In this example, the `HAVING`

clause includes only publishers whose average book ratings are greater than 4.5.

### 4) Using DB2 `AVG()`

function with other aggregate functions example

The following example returns the average book rating and the number of books by publishers with a condition that the average book rating is higher than 4 and the number of books is greater than 10.

```
SELECT
p.name publisher,
CAST(AVG(b.rating) AS DEC(4,2)) avg_rating,
COUNT(b.book_id) as book_count
FROM
books b
INNER JOIN publishers p
ON p.publisher_id = b.publisher_id
GROUP BY
p.name
HAVING
AVG(b.rating) > 4 AND
COUNT(b.book_id) > 10
ORDER BY
avg_rating DESC,
book_count DESC;
```

Here is the output:

In this tutorial, you have learned how to use the Db2 `AVG()`

function to calculate the average of a set of non-null values.