MySQL avg()

Introduction to AVG() in MySQL

Among many kinds of SQL languages, we use an aggregate function in Data Query Language (DQL) with SELECT statement. This kind of function performs the action 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, for getting 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() 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 following attributes. By 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	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

SELECT avg(salary) AS average_salary FROM employee;

Output:

Example #2

It will show the average salary according to each manager id

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

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

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

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

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

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 the Examples to implement avg() in MySQL along with the queries and outputs. You may also have a look at the following articles to learn more –

1. NOT in MySQL
2. Table in MySQL
3. Cursor in MySQL
4. Joins in MySQL
5. Cursor in MySQL | Examples
6. MySQL MAX() Function | How to Work?
7. Aggregate Functions in SQL