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 simple average.
SELECT AVG (aggregate_expression) FROM tables WHERE conditions;
Syntax 2: To calculate average and grouping 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 which is used 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 is above 150, but no two equal marks will be calculated twice, since we are using 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.
