DB2 - SQL COUNT(), AVG() and SUM() Functions

SQL COUNT(), AVG() and SUM() Functions

The COUNT() function returns the number of rows that matches a specified criterion.

The AVG() function returns the average value of a numeric column.

The SUM() function returns the total sum of a numeric column.

Let us see the syntax for each function below.

Syntax: COUNT()

SELECT COUNT(column_name)
  FROM table_name
  WHERE condition;

Syntax: AVG()

SELECT AVG(column_name)
  FROM table_name
  WHERE condition;

Syntax: SUM()

SELECT SUM(column_name)
  FROM table_name
  WHERE condition;

DB2 Database:

Below is a selection from the "Product" table in the DB2 database.

Example 1: Count Function

The following SQL statement finds the number of products in the "Product" table. NULL values are not counted.

SELECT COUNT(ProductID)
  FROM Products;

Result:

Example 2: Avg Function

The following SQL statement finds the average price of all products in the "Product" table. NULL values are ignored.

SELECT AVG(Price)
  FROM Products;

Result:

Example 3: Sum Function

The following SQL statement finds the sum of the "QtyOnHand" fields in the "Product" table. NULL values are ignored.

SELECT SUM(QtyOnHand)
  FROM Products;

Result:

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!