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 kind of 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 are going to 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 take any action 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. In this table, there are 3 attributes like section, name, and marks. With the help of aggregate function avg(), we will perform some examples for a better understanding of this function.
For getting average marks of 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, then we can use AVG() with 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. By 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 | Johjn | 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 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 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
In analysis perspective as well as in the extraction process from the database, those aggregated functions are very important as it helps us bring down complex scenarios. Specifically, when we give condition in the query or while using windows function, aggregated functions play major roles. Some functions like LIMIT, RANK, etc. and GROUP BY clause always comes with aggregated functions. While working on huge data like supply chain, we need to use these aggregated function always as it simplifies our data. The same also happens while using software like tableau or power BI, it always aggregates those values for better visualization.
Recommended Articles
This is a guide to AVG() in MySQL. Here we discuss how it functions, examples to implement avg() in MySQL along with queries and outputs in a concise way. You may also have a look at the following articles to learn more –
12 Online Courses | 10 Hands-on Projects | 92+ Hours | Verifiable Certificate of Completion
4.5
View Course
Related Courses