AVG

Syntax

AVG([ DISTINCT | ALL ] expr) OVER(analytic_clause)

Description

Returns the average of the values in the specified group, ignoring NULL values. The DISTINCT option can be used to return the average of distinct values of expr. If no rows match the condition, AVG() returns NULL.

Examples

CREATE TABLE EXPLOYEES(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
Query OK, 0 rows affected (0.081 sec)

INSERT INTO EXPLOYEES VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected (0.002 sec)

INSERT INTO EXPLOYEES VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected (0.002 sec)

INSERT INTO EXPLOYEES VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected (0.002 sec)

INSERT INTO EXPLOYEES VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected (0.002 sec)

SELECT LAST_NAME, AVG(SALARY) OVER(PARTITION BY JOB_ID) AVG_S FROM EXPLOYEES;
+-----------+------------+
| LAST_NAME | AVG_S      |
+-----------+------------+
| JIM       |  2000.0000 |
| MIKE      | 12000.0000 |
| LILY      | 12000.0000 |
| TOM       | 12000.0000 |
+-----------+------------+
4 rows in set (0.002 sec)