×
☰ See All Chapters

Oracle AVG Function

Oracle AVG function returns the average value in the specified column. Specified column should have numeric data type; MIN function does not accept columns having a data type other than numeric, such as character or date. When used with the DISTINCT command, the AVG function returns the average of the distinct rows.

The syntax of the AVG function is as follows:

SELECT AVG(<column-name>) FROM TABLE_NAME;

Oracle AVG Function Examples

Creating table for demonstrating AVG function

CREATE TABLE EMP_SALARY_COMMISION

(EMP_ID NUMBER(4) ,

SALARY NUMBER(7,2),

COMM NUMBER(7,2)

);

 

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('100', '1000', '110');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('101', '1000', '100');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('102', '1000');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('103', '1000', '20');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('104', '2000', '100');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('105', '2000');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('106', '2000');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('107', '3000', '60');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('108', '3000', '60');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('109', '3000');

 

COMMIT;

oracle-avg-function-0

AVG of all rows

SELECT AVG(COMM) FROM EMP_SALARY_COMMISION; --------- 75

AVG of distinct rows

SELECT AVG(DISTINCT COMM) FROM EMP_SALARY_COMMISION; --------- 72.50

Adding alias to AVG value

SELECT AVG(SALARY) AS AVERAGE_OF_ALL_SALARY FROM EMP_SALARY_COMMISION;

 

oracle-avg-function-1

All Chapters
Author