EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL avg()
Secondary Sidebar
MySQL Tutorial
  • Functions
    • MySQL Function
    • MySQL Aggregate Function
    • MySQL String functions
    • MySQL Date Functions
    • MySQL Window Functions
    • MySQL Math Functions
    • MySQL Boolean
    • Cursor in MySQL
    • Condition in MySQL
    • MySQL BETWEEN
    • Insert in MySQL
    • MySQL IFNULL()
    • MySQL TIMESTAMPDIFF()
    • MySQL COALESCE()
    • MySQL count()
    • MIN() in MySQL
    • MySQL Numeric
    • MySQL field()
    • MySQL FIND_IN_SET()
    • MySQL avg()
    • MySQL MAX() Function
    • MySQL BIN()
    • MySQL Concat
    • MySQL DECODE()
    • MySQL REGEXP_REPLACE()
    • MySQL Asynchronous
    • MySQL innodb_buffer_pool_size
    • MySQL key_buffer_size
    • MySQL TRUNCATE()
    • MySQL ROW_NUMBER()
    • NOT in MySQL
    • MySQL IN Operator
    • LIKE in MySQL
    • ANY in MySQL
    • MySQL NOT IN
    • MySQL CHECK Constraint
    • MySQL DISTINCT
    • MySQL ALL
    • MySQL Union
    • MySQL UNION ALL
    • MySQL EXISTS
    • MySQL ON DELETE CASCADE
    • MySQL REGEXP
    • MySQL Index
    • MySQL Add Index
    • MySQL REINDEX
    • MySQL UNIQUE INDEX
    • MySQL Clustered Index
    • MySQL? InnoDB Cluster
    • Table in MySQL
    • ALTER TABLE MySQL
    • MySQL Temporary Table
    • MySQL Clone Table
    • MySQL Repair Table
    • MySQL Lock Table
    • MySQL Optimize Table
    • TRUNCATE TABLE MySQL
    • MySQL?Table?Size
    • MySQL Table Dump
    • MySQL Update Set
    • MySQL ALTER TABLE Add Column
    • MySQL RANK()
    • MySQL CTE
    • MySQL LAG()
    • MySQL GROUP_CONCAT()
    • MySQL EXTRACT()
    • MySQL REPLACE
    • MySQL AUTO_INCREMENT
    • MySQL SYSDATE()
    • MySQL NULLIF()
    • MySQL Substring
    • MySQL SUBSTRING_INDEX()
    • MySQL LOWERCASE
    • MySQL Row
    • MySQL NOW
    • MySQL CEIL
    • MySQL Alias
    • MySQL Trigger
    • MySQL SHOW Triggers
    • MySQL UPDATE Trigger
    • MySQL DELETE Trigger
    • MySQL AFTER UPDATE Trigger
    • MySQL Stored Procedure
    • ROLLUP in MySQL
    • MySQL? INSTR()
    • MySQL Subquery
    • MySQL Timestamp
    • MySQL? Hour()
    • MySQL MOD()
    • MySQL DATE_FORMAT()
    • ALTER Column in MySQL
    • MySQL Rename Column
    • MySQL Interval
    • MySQL CURDATE
    • MySQL BIT
    • MySQL Binlog
    • MySQL Average
    • MySQL TEXT
    • MySQL SHOW
    • MySQL Offset
    • MySQL Timezone
    • mysql_real_escape_string
    • MySQL Datetime
    • MySQL DATE_SUB()
    • MySQL FULLTEXT
    • MySQL DATE_ADD()
    • MySQL sum()
    • MySQL Merge
    • MySQL BigInt
    • MySQL ROUND
    • MySQL VARCHAR
    • MySQL Decimal
    • MySQL Limit
    • MySQL today()
    • MySQL WEEKDAY
    • MySQL Split
    • MySQL Create Function
    • MySQL BLOB
    • MySQL encode()
    • MySQL Primary Key
    • MySQL Foreign Key
    • Unique Key in MySQL
    • MySQL Drop Foreign Key
    • MySQL DROP TRIGGER
    • MYSQL Database
    • Delete Database MySQL
    • MySQL Root
    • MySQL Root Password
    • MySQL Client
    • MySQL Users
    • MySQL?User Permissions
    • MySQL add user
    • MySQL List User
    • MySQL Show Users
    • MySQL User Password
    • MySQL?Cardinality
    • MySQL Workbench
    • MySQL Backup
    • MySQL REVOKE
    • MySQL Dump
    • MySQL Cluster
    • MySQL Full Text Search
    • MySQL Admin Tool
    • MySQL Export Database
    • MySQL Export to CSV
  • Basic
    • MySQL floor
    • MySQL DESCRIBE table
    • MySQL encryption
    • Introduction to MySQL
    • Is SQL Server a Database?
    • What is MySQL
    • Is MySQL Programming Language
    • MySQL Server
    • MySQL AB
    • MySQL Community Server
    • How To Install MySQL
    • MySQL Versions
    • MySQL OpenSource
    • MySQL GUI Tools
    • MySQL Grant
    • MySQL Error 1064
    • MySQL Drop Database
    • MySQL not equal
    • MySQL SELECT INTO Variable
    • MySQL Commands
    • MySQL Operators
    • What is MySQL Schema
    • Wildcards in MySQL
    • MySQL Constraints
    • MySQL Administration
    • MySQL Data Type
    • MYSQL COMMIT
    • MySQL FORMAT
    • Timestamp to Date in MySQL
    • MySQL DATEDIFF
    • MySQL?Incremental Backup
    • MySQL JSON Data Type
    • MySQL ENUM
    • MySQL Default Port
    • Cheat Sheet MySQL
  • Queries
    • MySQL Queries
    • MySQL Query Commands
    • SELECT in MySQL
    • MySQL INSERT IGNORE
    • MySQL having
    • ORDER BY in MySQL
    • MySQL Cheat Sheet
    • MySQL ORDER BY Random
    • MySQL ORDER BY DESC
    • MySQL GROUP BY
    • MySQL GROUP BY Count
    • MySQL GROUP BY month
    • MySQL WHERE Clause
    • MySQL WITH
    • MySQL FETCH
    • MySQL DDL
    • MySQL DML
    • MySQL WHERE IN Array
    • MySQL Fetch Array
    • MySQL ISNULL
    • MySQL Index Types
    • Mysql? Export Schema
    • Amazon RDS for MySQL
    • MySQL greatest
  • Database
    • What is Data Modeling
    • What is Data Processing
    • DBMS Architecture
    • DBMS Keys
    • Careers in Database Administration
    • What is MySQL Database
    • MySQL Relational Database
    • How to Connect Database to MySQL
    • MySQL Database Repair
    • RDBMS Interview Questions
    • DBMS Interview Questions
  • Joins
    • Joins in MySQL
    • MySQL Outer Join
    • Left Outer Join in MySQL
    • MySQL Self Join
    • Natural Join in MySQL
    • MySQL DELETE JOIN
    • MySQL Update Join
    • MySQL Cross Join
  • Advanced
    • MySQL Formatter
    • MySQL TINYINT
    • MySQL Grant All Privileges
    • MySQL DROP TABLE
    • MySQL rename database
    • MySQL Flush Privileges
    • MySQL super Privilege
    • MySQL Character Set
    • MySQL Log File
    • MySQL Flush Log
    • Grant Privileges MySQL
    • MySQL WHILE LOOP
    • IF Statement in MySQL
    • MySQL CASE Statement
    • MySQL IF Function
    • MySQL IF EXISTS
    • MySQL UUID
    • Views in MySQL
    • MySQL Replication
    • MySQL Partition
    • Toad for MySQL
    • Navicat for MySQL
    • MySQL AES_Encrypt
    • MySQL Performance Tuning
    • MySQL Transaction
    • MySQL? sort_buffer_size
    • MySQL? Sync
    • MySQL? Query Cache
    • MySQL Collation
    • MySQL ODBC Driver
    • MySQL Partitioning
    • MySQL InnoDB
    • MySQL Float vs Decimal
    • MySQL Union vs Union All
  • Interview Questions
    • MySQL Interview Questions

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

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

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

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 –

  1. NOT in MySQL
  2. Table in MySQL
  3. Cursor in MySQL
  4. Cursor in MySQL | Examples
Popular Course in this category
MySQL Training Program (12 Courses, 10 Projects)
  12 Online Courses |  10 Hands-on Projects |  92+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

MS SQL Training (16 Courses, 11+ Projects)4.9
Oracle Training (17 Courses, 8+ Projects)4.8
PL SQL Training (4 Courses, 2+ Projects)4.7
Primary Sidebar
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

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