Avg Function

In Oracle/PLSQL, the Avg function returns the average value of an expression.

The basic syntax when using the Avg function is:

SELECT Avg( expression )
FROM tables
WHERE predicates;

expression can be a numeric field or formula.

Apples To

• Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Simple Example

For example, you might wish to know how the average salary of all employees whose salary is above $25,000 / year.

SELECT Avg(salary) as "Avg Salary"
FROM employees
WHERE salary > 25000;

Example using DISTINCT

You can use the DISTINCT clause within the Avg function. For example, the SQL statement below returns the average salary of unique salary values where the salary is above $25,000 / year.

SELECT Avg(DISTINCT salary) as "Avg Salary"
FROM employees
WHERE salary > 25000;

If there were two salaries of $30,000/year, only one of these values were be used in the Avg function.

Example using a Formula

The expression contained within the Avg function does not need to be a single field. You could also use a formula. For example, you might want the average commission.

SELECT Avg(sales * 0.10) as "Average Commission"
FROM orders;

Example using GROUP BY

You could also use the Avg function to return the name of the department and the average sales (in the associated department). For example,

SELECT department, Avg(sales) as "Avg sales"
FROM order_details
GROUP BY department;

Because you have listed one column in your SELECT statement that is not encapsulated in the Avg function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.