w3resource

MySQL AVG() function

AVG() function

MySQL AVG() function retrieves the average value of a given expression. It is particularly useful for data analysis, reporting, and deriving insights from datasets. If the function does not find a matching row, it returns NULL.

This function is useful in -

  • This function helps in summarizing and presenting the central tendency of a dataset. A single value represents the average value of the data, which is more informative than looking at individual data points.
  • Business and financial analysis commonly uses it to calculate averages like sales, revenue, expenses, and profits and to evaluate performance over time.
  • The average can be used to compare different subsets of data. For example, you can compare the average salary of different departments or regions within a company.
  • Data trends and patterns can be identified by calculating the average of a time series dataset. This is useful for making predictions and informed decisions.
  • As part of a comprehensive data analysis, AVG() is often used with other aggregate functions such as SUM() and COUNT().
  • In manufacturing or quality control, the average can be used to assess the consistency and quality of products.
  • It's used in calculations like the standard deviation, coefficient of variation, and other statistical measures.
  • In research and survey analysis, the average helps to summarize responses and data from participants.
  • Average values are commonly used in creating various visualizations like bar charts, line graphs, and histograms.

Syntax:

AVG([DISTINCT] expr)

Where expr is a given expression. The DISTINCT option can be used to return the average of the distinct values of expr.

MySQL Version : 8.0

Contents:

Example : MySQL AVG() function

The following MySQL statement will return an average number of pages (of books) from the book_mast table.

Code:

SELECT AVG(no_page)
FROM book_mast;

Relational Algebra Expression:

Relational Algebra Expression: MySQL AVG() function.

Relational Algebra Tree:

Relational Algebra Tree: MySQL AVG() function.

Sample table: book_mast

+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| book_id | book_name                           | isbn_no     | cate_id | aut_id | pub_id | dt_of_pub  | pub_lang | no_page | book_price |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| BK001   | Introduction to Electrodynamics     | 0000979001  | CA001   | AUT001 | P003   | 2001-05-08 | English  |     201 |      85.00 |
| BK002   | Understanding of Steel Construction | 0000979002  | CA002   | AUT002 | P001   | 2003-07-15 | English  |     300 |     105.50 |
| BK003   | Guide to Networking                 | 0000979003  | CA003   | AUT003 | P002   | 2002-09-10 | Hindi    |     510 |     200.00 |
| BK004   | Transfer  of Heat and Mass          | 0000979004  | CA002   | AUT004 | P004   | 2004-02-16 | English  |     600 |     250.00 |
| BK005   | Conceptual Physics                  | 0000979005  | CA001   | AUT005 | P006   | 2003-07-16 | NULL     |     345 |     145.00 |
| BK006   | Fundamentals of Heat                | 0000979006  | CA001   | AUT006 | P005   | 2003-08-10 | German   |     247 |     112.00 |
| BK007   | Advanced 3d Graphics                | 0000979007  | CA003   | AUT007 | P002   | 2004-02-16 | Hindi    |     165 |      56.00 |
| BK008   | Human Anatomy                       | 0000979008  | CA005   | AUT008 | P006   | 2001-05-17 | German   |      88 |      50.50 |
| BK009   | Mental Health Nursing               | 0000979009  | CA005   | AUT009 | P007   | 2004-02-10 | English  |     350 |     145.00 |
| BK010   | Fundamentals of Thermodynamics      | 0000979010  | CA002   | AUT010 | P007   | 2002-10-14 | English  |     400 |     225.00 |
... ... ...
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+

Output:

mysql> SELECT AVG(no_page) FROM book_mast;
+--------------+
| AVG(no_page) |
+--------------+
|     286.6250 | 
+--------------+
1 row in set (0.02 sec)

Example: MySQL AVG() function with group by

MySQL AVG() function retrieves the average value of a given expression for each group if it is used with group by option. The following statement will return the average number of pages for each group of 'pub_id' from book_mast table.

Code:

SELECT pub_id,AVG(no_page)
FROM book_mast     
GROUP BY pub_id;

Relational Algebra Expression:

Relational Algebra Expression: MySQL AVG() function with group by.

Relational Algebra Tree:

Relational Algebra Tree: MySQL AVG() function with group by.

Sample table: book_mast

+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| book_id | book_name                           | isbn_no     | cate_id | aut_id | pub_id | dt_of_pub  | pub_lang | no_page | book_price |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| BK001   | Introduction to Electrodynamics     | 0000979001  | CA001   | AUT001 | P003   | 2001-05-08 | English  |     201 |      85.00 |
| BK002   | Understanding of Steel Construction | 0000979002  | CA002   | AUT002 | P001   | 2003-07-15 | English  |     300 |     105.50 |
| BK003   | Guide to Networking                 | 0000979003  | CA003   | AUT003 | P002   | 2002-09-10 | Hindi    |     510 |     200.00 |
| BK004   | Transfer  of Heat and Mass          | 0000979004  | CA002   | AUT004 | P004   | 2004-02-16 | English  |     600 |     250.00 |
| BK005   | Conceptual Physics                  | 0000979005  | CA001   | AUT005 | P006   | 2003-07-16 | NULL     |     345 |     145.00 |
| BK006   | Fundamentals of Heat                | 0000979006  | CA001   | AUT006 | P005   | 2003-08-10 | German   |     247 |     112.00 |
| BK007   | Advanced 3d Graphics                | 0000979007  | CA003   | AUT007 | P002   | 2004-02-16 | Hindi    |     165 |      56.00 |
| BK008   | Human Anatomy                       | 0000979008  | CA005   | AUT008 | P006   | 2001-05-17 | German   |      88 |      50.50 |
| BK009   | Mental Health Nursing               | 0000979009  | CA005   | AUT009 | P007   | 2004-02-10 | English  |     350 |     145.00 |
| BK010   | Fundamentals of Thermodynamics      | 0000979010  | CA002   | AUT010 | P007   | 2002-10-14 | English  |     400 |     225.00 |
... ... ...
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+

Output:

mysql> SELECT pub_id, AVG(no_page)
    -> FROM book_mast
    -> GROUP BY pub_id;
+--------+--------------+
| pub_id | AVG(no_page) |
+--------+--------------+
| P001   |     232.5000 | 
| P002   |     337.5000 | 
| P003   |     148.0000 | 
| P004   |     460.0000 | 
| P005   |     236.0000 | 
| P006   |     216.5000 | 
| P007   |     375.0000 | 
| P008   |     287.5000 | 
+--------+--------------+
8 rows in set (0.02 sec)

Pictorial Presentation

mysql average with group by example1 pictorial presentation

Example: MySQL AVG() function with distinct

MySQL AVG() function retrieves the unique average value of a given expression when used with DISTINCT keyword. The following statement will return the average of unique 'receive_qty' from the purchase table.

Code:

SELECT AVG(DISTINCT(receive_qty))
FROM purchase;

Relational Algebra Expression:

Relational Algebra Expression: MySQL AVG() function with distinct.

Relational Algebra Tree:

Relational Algebra Tree: MySQL AVG() function with distinct.

Sample table: purchase

+------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+
| invoice_no | invoice_dt | ord_no         | ord_date   | receive_dt | book_id | book_name                       | pub_lang | cate_id | receive_qty | purch_price | total_cost |
+------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+
| INV0001    | 2008-07-15 | ORD/08-09/0001 | 2008-07-06 | 2008-07-19 | BK001   | Introduction to Electrodynamics | English  | CA001   |          15 |       75.00 |    1125.00 |
| INV0002    | 2008-08-25 | ORD/08-09/0002 | 2008-08-09 | 2008-08-28 | BK004   | Transfer  of Heat and Mass      | English  | CA002   |           8 |       55.00 |     440.00 |
| INV0003    | 2008-09-20 | ORD/08-09/0003 | 2008-09-15 | 2008-09-23 | BK005   | Conceptual Physics              | NULL     | CA001   |          20 |       20.00 |     400.00 |
| INV0004    | 2007-08-30 | ORD/07-08/0005 | 2007-08-22 | 2007-08-30 | BK004   | Transfer  of Heat and Mass      | English  | CA002   |          15 |       35.00 |     525.00 |
| INV0005    | 2007-07-28 | ORD/07-08/0004 | 2007-06-25 | 2007-07-30 | BK001   | Introduction to Electrodynamics | English  | CA001   |           8 |       25.00 |     200.00 |
| INV0006    | 2007-09-24 | ORD/07-08/0007 | 2007-09-20 | 2007-09-30 | BK003   | Guide to Networking             | Hindi    | CA003   |          20 |       45.00 |     900.00 |
+------------+------------+----------------+------------+------------+---------+---------------------------------+----------+---------+-------------+-------------+------------+

Output:>

mysql> SELECT AVG(DISTINCT(receive_qty))
    -> FROM purchase;
+----------------------------+
| AVG(DISTINCT(receive_qty)) |
+----------------------------+
|                    14.3333 | 
+----------------------------+
1 row in set (0.02 sec)

Pictorial Presentation

mysql average with group by example2 pictorial presentation

Example: MySQL AVG() function decimal places

Here we have discussed how to use ROUND() along with AVG() to retrieve a value calculated upto a specific number of decimal places of a given value. The following statement will return the average number of pages up to 2 decimal places for each group of 'pub_id' from book_mast table.

Code:


SELECT pub_id,ROUND(AVG(no_page),2)
FROM book_mast          
GROUP BY pub_id;

Sample table: book_mast

+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| book_id | book_name                           | isbn_no     | cate_id | aut_id | pub_id | dt_of_pub  | pub_lang | no_page | book_price |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| BK001   | Introduction to Electrodynamics     | 0000979001  | CA001   | AUT001 | P003   | 2001-05-08 | English  |     201 |      85.00 |
| BK002   | Understanding of Steel Construction | 0000979002  | CA002   | AUT002 | P001   | 2003-07-15 | English  |     300 |     105.50 |
| BK003   | Guide to Networking                 | 0000979003  | CA003   | AUT003 | P002   | 2002-09-10 | Hindi    |     510 |     200.00 |
| BK004   | Transfer  of Heat and Mass          | 0000979004  | CA002   | AUT004 | P004   | 2004-02-16 | English  |     600 |     250.00 |
| BK005   | Conceptual Physics                  | 0000979005  | CA001   | AUT005 | P006   | 2003-07-16 | NULL     |     345 |     145.00 |
| BK006   | Fundamentals of Heat                | 0000979006  | CA001   | AUT006 | P005   | 2003-08-10 | German   |     247 |     112.00 |
| BK007   | Advanced 3d Graphics                | 0000979007  | CA003   | AUT007 | P002   | 2004-02-16 | Hindi    |     165 |      56.00 |
| BK008   | Human Anatomy                       | 0000979008  | CA005   | AUT008 | P006   | 2001-05-17 | German   |      88 |      50.50 |
| BK009   | Mental Health Nursing               | 0000979009  | CA005   | AUT009 | P007   | 2004-02-10 | English  |     350 |     145.00 |
| BK010   | Fundamentals of Thermodynamics      | 0000979010  | CA002   | AUT010 | P007   | 2002-10-14 | English  |     400 |     225.00 |
... ... ...
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+

Output:

mysql> SELECT pub_id, ROUND(AVG(no_page),2)
    -> FROM book_mast
    -> GROUP BY pub_id;
+--------+-----------------------+
| pub_id | ROUND(AVG(no_page),2) |
+--------+-----------------------+
| P001   |                232.50 | 
| P002   |                337.50 | 
| P003   |                148.00 | 
| P004   |                460.00 | 
| P005   |                236.00 | 
| P006   |                216.50 | 
| P007   |                375.00 | 
| P008   |                287.50 | 
+--------+-----------------------+
8 rows in set (0.00 sec)

Example: MySQL AVG() function with COUNT() function

Here we have discussed how to use MySQL AVG() function with COUNT() function to fetch suitable data. The following statement will return the average 'no_page' and number of the publisher for each group of the publisher from book_mast table.

Code:

SELECT pub_id,COUNT(pub_id),AVG(no_page)
FROM book_mast
GROUP BY pub_id;

Relational Algebra Expression:

Relational Algebra Expression: MySQL AVG() function with COUNT() function.

Relational Algebra Tree:

Relational Algebra Tree: MySQL AVG() function with COUNT() function.

Sample table: book_mast

+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| book_id | book_name                           | isbn_no     | cate_id | aut_id | pub_id | dt_of_pub  | pub_lang | no_page | book_price |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| BK001   | Introduction to Electrodynamics     | 0000979001  | CA001   | AUT001 | P003   | 2001-05-08 | English  |     201 |      85.00 |
| BK002   | Understanding of Steel Construction | 0000979002  | CA002   | AUT002 | P001   | 2003-07-15 | English  |     300 |     105.50 |
| BK003   | Guide to Networking                 | 0000979003  | CA003   | AUT003 | P002   | 2002-09-10 | Hindi    |     510 |     200.00 |
| BK004   | Transfer  of Heat and Mass          | 0000979004  | CA002   | AUT004 | P004   | 2004-02-16 | English  |     600 |     250.00 |
| BK005   | Conceptual Physics                  | 0000979005  | CA001   | AUT005 | P006   | 2003-07-16 | NULL     |     345 |     145.00 |
| BK006   | Fundamentals of Heat                | 0000979006  | CA001   | AUT006 | P005   | 2003-08-10 | German   |     247 |     112.00 |
| BK007   | Advanced 3d Graphics                | 0000979007  | CA003   | AUT007 | P002   | 2004-02-16 | Hindi    |     165 |      56.00 |
| BK008   | Human Anatomy                       | 0000979008  | CA005   | AUT008 | P006   | 2001-05-17 | German   |      88 |      50.50 |
| BK009   | Mental Health Nursing               | 0000979009  | CA005   | AUT009 | P007   | 2004-02-10 | English  |     350 |     145.00 |
| BK010   | Fundamentals of Thermodynamics      | 0000979010  | CA002   | AUT010 | P007   | 2002-10-14 | English  |     400 |     225.00 |
... ... ...
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+

Output:

mysql> SELECT pub_id, COUNT(pub_id),AVG(no_page)
    -> FROM book_mast
    -> GROUP BY pub_id;
+--------+---------------+--------------+
| pub_id | COUNT(pub_id) | AVG(no_page) |
+--------+---------------+--------------+
| P001   |             2 |     232.5000 | 
| P002   |             2 |     337.5000 | 
| P003   |             2 |     148.0000 | 
| P004   |             2 |     460.0000 | 
| P005   |             2 |     236.0000 | 
| P006   |             2 |     216.5000 | 
| P007   |             2 |     375.0000 | 
| P008   |             2 |     287.5000 | 
+--------+---------------+--------------+
8 rows in set (0.00 sec)

MySQL AVG() function with having

MySQL AVG() function retrieves the average value of a given expression against a condition specified after HAVING clause for each group specified after the GROUP BY clause. This way you can use HAVING and GROUP BY with MySQL AVG() function. The following statement will return the average no_page for those group whose 'pub_id' is 'P008' from the book_mast table.

Code:

SELECT pub_id, AVG(no_page)
FROM book_mast
GROUP BY pub_id
HAVING  pub_id='P008';

Relational Algebra Expression:

Relational Algebra Expression: MySQL AVG() function with having.

Relational Algebra Tree:

Relational Algebra Tree: MySQL AVG() function with having.

Sample table: book_mast

+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| book_id | book_name                           | isbn_no     | cate_id | aut_id | pub_id | dt_of_pub  | pub_lang | no_page | book_price |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| BK001   | Introduction to Electrodynamics     | 0000979001  | CA001   | AUT001 | P003   | 2001-05-08 | English  |     201 |      85.00 |
| BK002   | Understanding of Steel Construction | 0000979002  | CA002   | AUT002 | P001   | 2003-07-15 | English  |     300 |     105.50 |
| BK003   | Guide to Networking                 | 0000979003  | CA003   | AUT003 | P002   | 2002-09-10 | Hindi    |     510 |     200.00 |
| BK004   | Transfer  of Heat and Mass          | 0000979004  | CA002   | AUT004 | P004   | 2004-02-16 | English  |     600 |     250.00 |
| BK005   | Conceptual Physics                  | 0000979005  | CA001   | AUT005 | P006   | 2003-07-16 | NULL     |     345 |     145.00 |
| BK006   | Fundamentals of Heat                | 0000979006  | CA001   | AUT006 | P005   | 2003-08-10 | German   |     247 |     112.00 |
| BK007   | Advanced 3d Graphics                | 0000979007  | CA003   | AUT007 | P002   | 2004-02-16 | Hindi    |     165 |      56.00 |
| BK008   | Human Anatomy                       | 0000979008  | CA005   | AUT008 | P006   | 2001-05-17 | German   |      88 |      50.50 |
| BK009   | Mental Health Nursing               | 0000979009  | CA005   | AUT009 | P007   | 2004-02-10 | English  |     350 |     145.00 |
| BK010   | Fundamentals of Thermodynamics      | 0000979010  | CA002   | AUT010 | P007   | 2002-10-14 | English  |     400 |     225.00 |
... ... ...
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+

Output:

mysql> SELECT pub_id, AVG(no_page)
    -> FROM book_mast
    -> GROUP BY pub_id HAVING pub_id='P008';
+--------+--------------+
| pub_id | AVG(no_page) |
+--------+--------------+
| P008   |     287.5000 | 
+--------+--------------+
1 row in set (0.00 sec)

Previous: Aggregate Functions and Grouping
Next: BIT_AND()



Follow us on Facebook and Twitter for latest update.