Oracle AVG Function

Summary: in this tutorial, you will learn how to use the Oracle AVG() function to calculate the average of a group.

Introduction to Oracle AVG() function syntax #

The Oracle AVG() function accepts a list of values and returns the average.

Here’s the syntax of the Oracle AVG() function:

AVG([DISTINCT | ALL ] expression)Code language: SQL (Structured Query Language) (sql)

The AVG() function can accept a clause which is either DISTINCT or ALL.

The DISTINCT clause instructs the function to ignore the duplicate values while the ALL clause causes the function to consider all the duplicate values.

For example, the average DISTINCT of 1, 1, 2, and 3 is (1 + 2 + 3 ) / 3 = 2, while the average ALL of 1, 1, 2, and 3 is (1 + 1 + 2 + 3) /4 = 1.75

The AVG() function ignores NULL values. For example, the average of 2, 4, and NULL is (2 + 4) /2 = 3.

Oracle AVG() function examples #

We will use the products table in the sample database for the demonstration.

Basic Oracle AVG() function example #

The following example uses the AVG function to calculate the average standard costs of all products:

SELECT
  ROUND(AVG(standard_cost), 2) avg_std_cost
FROM
  products;Code language: SQL (Structured Query Language) (sql)

Try it

Notice that we used the ROUND() function to return the average standard cost rounded to 2 decimal places.

You can also use multiple AVG() functions within the same query. For example, the following statement calculates the averages of standard costs and list prices:

SELECT
  ROUND(AVG(standard_cost), 2) avg_std_cost,
  ROUND(AVG(list_price), 2) avg_list_price
FROM
  products;Code language: SQL (Structured Query Language) (sql)

Try it

Oracle AVG() function with DISTINCT clause #

The following statement calculates the average DISTINCT list prices:

SELECT
  ROUND(AVG(DISTINCT list_price), 2) avg_list_price
FROM
  products;Code language: SQL (Structured Query Language) (sql)

Try it

The result of the average DISTINCT is different from the average ALL above because some products have the same list prices.

Oracle AVG() function with GROUP BY clause #

The following example calculates the average list price of products by category:

SELECT
    category_id,
    ROUND(
        AVG( list_price ),
        2
    ) avg_list_price
FROM
    products
GROUP BY
    category_id;
Code language: SQL (Structured Query Language) (sql)

Try it

In this example, the GROUP BY clause divides the products by category and then the AVG() function returns the average for each group.

To make the result more readable, you can also retrieve the category name by adding an INNER JOIN clause to the query above:

SELECT
  category_name,
  ROUND(AVG(list_price), 2) avg_list_price
FROM
  products
  INNER JOIN product_categories USING (category_id)
GROUP BY
  category_name
ORDER BY
  category_name;Code language: SQL (Structured Query Language) (sql)

Try it

Oracle AVG() function with HAVING clause #

The following example returns the product categories whose average list prices are greater than 1000 specified by the HAVING clause:

SELECT
  category_name,
  ROUND(AVG(list_price), 2) avg_list_price
FROM
  products
  INNER JOIN product_categories USING (category_id)
GROUP BY
  category_name
HAVING
  AVG(list_price) > 1000
ORDER BY
  category_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

#

The following HAVING clause is used to filter groups of product categories.

HAVING
        AVG( list_price )> 1000 
Code language: SQL (Structured Query Language) (sql)

Using Oracle AVG() function in subqueries #

Consider the following example:

SELECT
  ROUND(AVG(avg_list_price), 2) avg_of_avg
FROM
  (
    SELECT
      AVG(list_price) avg_list_price
    FROM
      products
    GROUP BY
      category_id
  );Code language: SQL (Structured Query Language) (sql)

Try it

In this example:

• The subquery returns the average list prices by product category.
• The outer query returns the average of the average list prices per product category.

Using Oracle AVG() function with NULL values #

Let’s create a new table called performances for the demonstration.

CREATE TABLE performances (
  employee_id NUMBER PRIMARY KEY,
  score NUMBER (3, 1)
);

INSERT INTO
  performances (employee_id, score)
VALUES
  (1, 95);

INSERT INTO
  performances (employee_id, score)
VALUES
  (2, 70);

INSERT INTO
  performances (employee_id, score)
VALUES
  (3, 60);

INSERT INTO
  performances (employee_id, score)
VALUES
  (4, NULL);

SELECT
  *
FROM
  performances;Code language: SQL (Structured Query Language) (sql)

Try it

The following statement uses the AVG function to calculate the average score of all employees:

SELECT
  AVG(score)
FROM
  performances;Code language: SQL (Structured Query Language) (sql)

Output:

The output indicates that the the AVG() function ignores NULL.

Using Oracle AVG() with NVL() function #

If you want to treat the NULL value as zero for calculating the average, you can use AVG() function together with the NVL() function:

SELECT
  AVG(NVL (score, 0))
FROM
  performances;Code language: SQL (Structured Query Language) (sql)

Try it

The NVL() function returns 0 if the score is NULL.

Output:

Summary #

• Use the Oracle AVG() function to calculate the average of a group of values.
• Use the DISTINCT to calculate the average of distinct values in a set.
• Use the ALL option to calculate the average of all values.