AVG function in Oracle

by

AVG is one of the vital Numeric/Math functions of Oracle. It is used to get the average value of an expression. The AVG function is supported in the various versions of the Oracle/PLSQL, including, Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, and Oracle 8i.

Syntax: To calculate the simple average.

SELECT AVG (aggregate_expression)
FROM tables
WHERE conditions;

Syntax 2: Calculate the average and group the results by one or more columns.

SELECT expression1, expression2, ... expression_n,
AVG(aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;

Parameters:
expression1, expression2, … expression_n It is used to specify the expressions to be included in the GROUP BY clause but is not encapsulated in the AVG function.
aggregate_expression: It is used to specify the column or expression that will be averaged.
tables: It is used to specify the tables to retrieve records from.
conditions: It is an optional parameter to specify the conditions that must be met for selection.

Example 1: 

SELECT AVG(marks) AS "Avg Marks"
FROM students
WHERE marks > 150;

Explanation:
Here we are calculating the average marks of all the students whose marks is above 150.

Example 2: 

SELECT AVG( DISTINCT marks) AS "Avg Marks"
FROM students
WHERE marks > 150;

Explanation:
Here we are calculating the average marks of all the students whose marks are above 150, but no two equal marks will be calculated twice since we are using the DISTINCT clause.

Example 3: 

SELECT AVG((marks * 100)/500) AS "Avg Marks"
FROM students
WHERE marks > 150;

Explanation:
Here we are calculating the average marks of all the students whose marks is above 150, using a formula. Thus the average will be calculated for the results of the mentioned formula.

Example 4: 

SELECT class, AVG(marks) AS "Avg Marks"
FROM students
GROUP BY class;

Explanation:
Here we will get the name of the class and the average marks in the associated class since we are using the GROUP BY clause.