Oracle AVG function

The Oracle/PLSQL AVG function returns the average value of the expression.

Oracle/PLSQL syntax of AVG function

SELECT AVG(aggregate_expression_id)
FROM tabs
[WHERE conds]

OR AVG function syntax with results grouped by one or more columns:

SELECT expression1_id, expression2_id, ... expression_n_id,
AVG(aggregate_expression_id)
FROM tabs
[WHERE conds]
GROUP BY expression1_id, expression2_id, ... expression_n_id;

Parameters and arguments of the function

• expression1_id, expression2_id, … expression_n_id – Expressions that are not encapsulated in the AVG function must be included in the GROUP BY operator at the end of the SQL sentence.
• aggregate_expression_id – is a column or expression to be averaged.
• tabs – tables from which you want to get records. At least one table must be specified in FROM sentence.
• WHERE conds – are optional. These are the conditions that must be met for the selected records.

The AVG function returns a numeric value.

The AVG function can be used in the following Oracle/PLSQL versions

One Field Example

Let’s consider some examples of the AVG function and learn how to use the AVG function in Oracle/PLSQL.

For example, you can find out what is the average salary of all employees whose salary exceeds $18000 per year.

SELECT AVG(salary_id) AS "Avg Salary"
FROM empls
WHERE salary_id > 18,000;

In this example of the AVG function, we called the expression AVG(salary_id) as “Avg Salary”. As a result, “Avg Salary” will be displayed as the field name when the resulting set is returned.

Example – using DISTINCT

You can use DISTINCT operator in AVG function. For example, the following SQL statement returns the average salary with unique values of salary, where the salary exceeds $18000 per year.

In the example below, the average salary of employees from the empls table is calculated.

SELECT AVG(DISTINCT salary_id) AS "Avg Salary"
FROM empls
WHERE salary_id > 18,000;

Example – using formula

The expression contained in the AVG function does not have to be a single field. You can also use a formula. For example, you may need an average commission.

SELECT AVG(sales * 0.10) AS "Average Commission"
FROM ords;

Example – using GROUP BY

You can also use the AVG function to get the department name and average sales (in the corresponding department). For example.

SELECT depart, AVG(sales_id) AS "Avg sales".
FROM ord_details
GROUP BY depart;

Since your SELECT operator has one column that is not encapsulated in the AVG function, you must use GROUP BY. Therefore, the department field must be specified in the GROUP BY operator.

Using the AVG function (Introduction to Oracle SQL)