Updated May 10, 2023
Introduction to AVG() in MySQL
Among many kinds of SQL languages, we use an aggregate function in Data Query Language (DQL) with a SELECT statement. This function acts on multiple values or records and returns a single value. The single value may be of sum, average, minimum, or maximum among all the values. In this topic, we will learn about MySQL avg().
Example: if we have records of all the citizens of India, both district and state-wise, forgetting the total strength of India, we need to use the aggregate function like SUM(). In the same manner, if we want to know the highest, lowest, or average population, either state or district-wise, we have to use functions like MAX(), MIN(), and AVG(), respectively. But this aggregate function does not act on null values except functions like COUNT(). There is another function called GROUP BY(); in many scenarios, aggregate functions are used along with this.
Syntax:
SELECT avg(sales) FROM product;
OR
SELECT * FROM product
GROUP BY product_name
HAVING avg(sales) > 1000;
How does avg() work in MySQL?
Now, let us discuss how avg() works in MySQL?
section | name | marks |
Sec-b | abcd | 90 |
Sec-c | edfg | 86 |
Sec-C | lkdf | 94 |
Sec-c | odjfoi | 78 |
Sec-a | lnknc | 60 |
Sec-b | lkdjv | 86 |
Sec-c | jshjdh | 92 |
Sec-a | jkhcj | 89 |
Sec-b | oijcoid | 76 |
Above is the student table database. This table has three attributes: section, name, and marks. With the help of the aggregate function avg(), we will perform some examples for a better understanding of this function.
To get average marks from all the students:
Syntax:
SELECT AVG(marks) AS avg_marks
FROM student;
Output:
Avg_marks |
83.44 |
If we want the average mark of students for each section, we can use AVG() with the GROUP BY function.
Syntax:
SELECT section, AVG(marks) AS avg_marks
FROM student
GROUP BY section;
Output:
section | Avg_marks |
Sec-b | 82.5 |
Sec-c | 89 |
Sec-a | 81 |
Examples to Implement avg() in MySQL
Let’s create another employee table with the following attributes. Deep-diving into this table can give us depth knowledge about avg() aggregate function.
emloyee_id | first_name | last_name | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id | |
100 | ste abcd | King | SKING | 515.123.4567 | 6/17/1987 | AD_PRES | 2400 | Null | Null | 90 |
101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 9/21/1989 | AD_VP | 17000 | Null | 100 | 90 |
102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 1/13/1993 | AD_VP | 17000 | Null | 100 | 90 |
108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 8/17/1994 | FI_MGR | 12000 | Null | 101 | 100 |
114 | Den | Raphaely | DRAPHEAL | 515.127.4561 | 12/7/1994 | PU_MAN | 11000 | Null | 100 | 30 |
145 | John | Russelllsd | JRUsdfSSEL | 011.44.176344.429268 | 10/1/1995 | SA_MArN | 14000 | 0.4 | 100 | 80 |
146 | Kafren | Partnerrs | KcPARTNER | 011.44.134454.467268 | 1/5/1997 | SA_MAN | 13500 | 0.3 | 100 | 80 |
147 | Albgferto | Errgdazuriz | AERRdgdgAZUR | 011.43544.1344.429278 | 3/10/1997 | SA_MAN | 12000 | 0.3 | 100 | 80 |
148 | Gefdrald | Cadmbrault | GCAMBRAdfdvU | 011.44545.1344.619268 | 10/15/1999 | SA_MAN | 11000 | 0.3 | 100 | 80 |
149 | Elendgdi | Zdglotkey | EZLOgTKEY | 011.4445.1344.429018 | 1/29/2000 | SA_MAN | 10500 | 0.2 | 100 | 80 |
162 | Clara | Vishney | CVISHNEY | 011.44.1346.129268 | 11/11/1997 | SA_REP | 10500 | 0.25 | 147 | 80 |
168 | Lisa | Ozer | LOZER | 011.44.1343.929268 | 3/11/1997 | SA_REP | 11500 | 0.25 | 148 | 80 |
174 | Ellen | Abel | EABEL | 011.44.1644.429267 | 5/11/1996 | SA_REP | 11000 | 0.3 | 149 | 80 |
201 | Michael | Hartstein | MHARTSTE | 515.123.5555 | 2/17/1996 | MK_MAN | 13000 | Null | 100 | 20 |
205 | Shelley | Higgins | SHIGGINS | 515.123.8080 | 6/7/1994 | AC_MGR | 12000 | Null | 101 | 110 |
Example #1
It will show the average salary of all employees
Code:
SELECT avg(salary) AS average_salary FROM employee;
Output:
Example #2
It will show the average salary according to each manager’s id
Code:
SELECT manager_id, avg(salary) FROM employee
GROUP BY manager_id;
Output:
Example #3
It will show the average salary for each manager id whose average salary will be greater than 11000
Code:
SELECT manager_id, avg(salary) FROM employee
GROUP BY manager_id
HAVING avg(salary) > 11000;
Output:
Example #4
It will show the average salary according to each department’s id
Code:
SELECT department_id, avg(salary) FROM employee
GROUP BY department_id;
Output:
Example #5
It will show the average salary according to each department id where the average salary will be greater than 11500
Code:
SELECT department_id, avg(salary) FROM employee
GROUP BY department_id
HAVING avg(salary)>11500;
Output:
Example #6
It will show the average salary according to each job id
Code:
SELECT job_id, avg(salary) FROM employee
GROUP BY job_id;
Output:
Example #7
It will show the average salary according to each job id where the average salary will be greater than 12000
Code:
SELECT job_id, avg(salary) FROM employee
GROUP BY job_id
HAVING avg(salary)>12000;
Output:
Conclusions
From an analysis perspective and in the extraction process from the database, those aggregated functions are essential as it helps us bring down complex scenarios. Specifically, aggregated functions play major roles when we give conditions in the query or while using the Windows function. Some functions like LIMIT, RANK, etc., and GROUP BY clause always comes with aggregated functions. While working on huge data like supply chains, we need to use these aggregated functions always as it simplifies our data. The same happens using Tableau or Power BI; it always aggregates those values for better visualization.
Recommended Articles
We hope that this EDUCBA information on “MySQL avg()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.