Db2 AVG

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)
Code language: SQL (Structured Query Language) (sql)

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);
Code language: SQL (Structured Query Language) (sql)

Second, insert four rows into the avg_demo table:

INSERT INTO avg_demo(c1) VALUES(1),(2),(3),(NULL);
Code language: SQL (Structured Query Language) (sql)

Third, view the data from the avg_demo table by using the following SELECT statement:

SELECT c1 FROM avg_demo;
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

AVERAGE ----------- 2
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

AVG_BOOK_RATING ------------------------------- 4.00466544454628780934922089825
Code language: SQL (Structured Query Language) (sql)

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;
Code language: SQL (Structured Query Language) (sql)

Now the output is much much more readable:

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

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;
Code language: SQL (Structured Query Language) (sql)

This picture shows the partial output:

db2 avg function with group by clause example

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;
Code language: SQL (Structured Query Language) (sql)
db2 avg function with having clause example

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;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

db2 avg function with count function example

In this tutorial, you have learned how to use the Db2 AVG() function to calculate the average of a set of non-null values.

Was this tutorial helpful ?