MySQL avg()

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	email	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.

1. NOT in MySQL
2. Table in MySQL
3. Cursor in MySQL
4. Cursor in MySQL | Examples