SQL AGGREGATE FUNCTIONS – AVG
SQL has 5 different aggregate functions (MIN, MAX, COUNT, SUM & AVG). Here we will be looking at the AVG function. The AVG function returns the average of all the values of a column in the table.
SYNTAX –
SELECT AVG(column1) FROM table_name WHERE condition;
Usually, the aggregate functions are used in conjunction with the GROUP BY/ HAVING clause, which gives even more meaningful results. The syntax for the query using AVG function using GROUP BY / HAVING clause is mentioned below –
SELECT column1, AVG(column2) FROM table_name WHERE condition1 GROUP BY column1 HAVING condition2;
At first, these might look little complicated, but once we go over few examples, it will make more sense.
Illustration – Consider the below employee table in the database which stores the information pertaining to employees in ABC company –
| ID | employee_name | age | salary | Department | manager_name |
|---|---|---|---|---|---|
| 1 | Jimmy | 32 | 80000 | Sales | Vijay |
| 2 | James | 35 | 85000 | Sales | Vijay |
| 3 | Helen | 33 | 82000 | HR | Krish |
| 4 | Mary | 26 | 78000 | Quality | Warner |
| 5 | William | 33 | 90000 | HR | Krish |
| 6 | Vijay | 38 | 105000 | Sales | Roger |
| 7 | Krish | 38 | 105000 | HR | Roger |
Query 1 – What is the average salary of all the employees of ABC company?
SELECT AVG(salary) FROM employee;
AVG(salary)
—————
89285.71428571429
Query 2 – What is the average salary of all employees of ABC company who belongs to sales department?
SELECT AVG(salary) FROM employee WHERE department=’sales’;
AVG(salary)
—————
90000
Query 3 – What is the average salary of employees in ABC company by each department.
SELECT Department, AVG(salary) FROM employee GROUP BY Department;
| Department | AVG(salary) |
|---|---|
| Sales | 90000 |
| HR | 92333.33333 |
| Quality | 78000 |
Query 4 – What is the list of departments at ABC company, which has more than average employee salary less than or equal to 90000 in their department?
SELECT Department, AVG(salary) FROM employee GROUP BY Department HAVING AVG(salary) <= 90000;
| Department | AVG(salary) |
|---|---|
| Sales | 90000 |
| Quality | 78000 |