SQL: AVG FUNCTION

SQL AVG functions with syntax and examples.

DESCRIPTION

SQL AVG function used to get the average of an expression of SELECT statement.

SYNTAX : for the SQL AVG function is:

SELECT AVG(expression) FROM tables WHERE conditions;

expression will be a numeric fields or formula.

EXAMPLE - WITH SINGLE  VALUE EXPRESSION

For example, you might wish to know how the average salary of all employee that are in the assosiate designation.

SELECT AVG(salary) AS "Average Salary" FROM employee WHERE designation = ‘assosiate’;

SQL Example alias AVG(salary) expression as "Average salary ". As a result, "Average salary" will display as the field name when the result set is returned.

EXAMPLE - USING FORMULA

SQL AVG function is not needed to be a single field. You can use it is a formula. For example, you might want the average profit for a product. Average profit is calculated as In Hand salary less cost.

SELECT AVG(salary - tax) AS "In Hand Salary" FROM employee;

You can also want to perform a mathematical operation in  AVG function. For example, you can calculate the 12 month average salary

SELECT AVG(salary * 12) AS “Yearly” FROM employee;

EXAMPLE – USING SQL GROUP BY

Many time its required to use the SQL GROUP BY clause with the AVG function.

e.g. , AVG function to return the name of the department and the average sales (in the associated department).

SELECT department, AVG(sales) AS "Average Sales" FROM order_details GROUP BY department;