AVG

Purpose

This function returns the mean value.

Syntax

avg::=

Avg

Usage Notes

  • With DISTINCT, the function considers duplicate values of expr only once. It considers all occurrences with ALL. If the function contains neither ALL nor DISTINCT, the default is ALL.
  • This function accepts operands of types numeric and interval.

Examples

Aggregate Function

Copy 
SELECT 
       department, 
       AVG(starting_salary) AVG
FROM employee_table GROUP BY department;
Result
DEPARTMENT AVG
ACCOUNTS 46000
HR 41250

Analytic Function

Copy 
SELECT 
       id, department, hire_date, starting_salary, 
       AVG(starting_salary) OVER (PARTITION BY department ORDER BY hire_date) AVG
FROM employee_table ORDER BY department, hire_date;
Result
ID DEPARTMENT HIRE_DATE STARTING_SALARY AVG
2005 ACCOUNTS 2013-01-01 30000 30000
2003 ACCOUNTS 2015-07-01 50000 40000
2002 ACCOUNTS 2017-01-01 40000 47500
2004 ACCOUNTS 2017-01-01 70000 47500
2001 ACCOUNTS 2018-07-01 40000 46000
1003 HR 2014-01-01 35000 35000
1002 HR 2016-01-01 45000 38333.333
1004 HR 2016-01-01 35000 38333.333
1001 HR   50000 41250