MySQL AVG Function

MySQL 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;

There should be no space between AVG and left Brackets (Parentheses), otherwise it is an error.

MySQL AVG Function Examples

Creating table for demonstrating AVG function

CREATE TABLE EMP_SALARY_COMMISION

(EMP_ID numeric(4) ,

SALARY numeric(7,2),

COMM numeric(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;

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;

All Chapters