DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Db2 Aggregate Functions » Db2 AVG

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)

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:

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;
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;

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 ?
  • YesNo
Next Db2 COUNT

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.