EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL avg()

MySQL avg()

By Aanchal SharmaAanchal Sharma

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

MySQL avg() output 1

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:

MySQL avg() output 2

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:

MySQL avg() output 3

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:

MySQL avg() output 4

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:

output 5

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:

output 6

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:

output 7

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
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle1500+ Hour of HD Videos | 80 Learning Paths | 360+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more