Syntax
AVG([ DISTINCT | ALL ] expr) OVER(analytic_clause)
Purpose
AVG() returns the average value of a specified group. NULLs are ignored. The DISTINCT option can be used to return the average of distinct values of expr. If no matching rows are found, AVG() returns NULL.
Examples
obclient> CREATE TABLE employees (last_name CHAR(10), salary DECIMAL, job_id CHAR(32));
Query OK, 0 rows affected
obclient> INSERT INTO employees VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected
obclient> INSERT INTO employees VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected
obclient> INSERT INTO employees VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected
obclient> INSERT INTO employees VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected
obclient> SELECT LAST_NAME, AVG(SALARY) OVER(PARTITION BY JOB_ID) AVG_S FROM employees;
+-----------+------------+
| LAST_NAME | AVG_S |
+-----------+------------+
| JIM | 2000.0000 |
| MIKE | 12000.0000 |
| LILY | 12000.0000 |
| TOM | 12000.0000 |
+-----------+------------+
4 rows in set