EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL avg()
 

MySQL avg()

Aanchal Sharma
Article byAanchal Sharma
EDUCBA
Reviewed byRavi Rathore

Updated May 10, 2023

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().

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - 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
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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
EDUCBA

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

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW