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:


-- This SQL statement calculates the average value of the no_page column in the book_mast table.
SELECT AVG(no_page)
-- Specifies the aggregate function AVG() to calculate the average value of the specified column, which is no_page in this case.
FROM book_mast;
-- Specifies the table from which to retrieve data, which is the book_mast table.

Explanation:

  • The purpose of this SQL code is to calculate the average value of the no_page column in the book_mast table.

  • SELECT AVG(no_page): This line specifies the use of the AVG() aggregate function to calculate the average value of the no_page column. The AVG() function returns the average of all non-NULL values in the specified column.

  • FROM book_mast: This line specifies the table from which to retrieve the data, which is the book_mast table.

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:


-- This SQL query calculates the average number of pages for each publisher's books.
SELECT pub_id, AVG(no_page) -- Selects the publisher ID and calculates the average number of pages for each publisher's books
FROM book_mast -- Specifies the table from which to retrieve data (book_mast table)
GROUP BY pub_id; -- Groups the results by publisher ID, so that the average number of pages is calculated for each publisher separately

Explanation:

  • This SQL query retrieves data from the book_mast table.

  • It calculates the average number of pages (AVG(no_page)) for books published by each publisher (pub_id).

  • The GROUP BY clause ensures that the results are grouped by publisher ID, allowing for separate averages to be calculated for each publisher.

  • Here's how the process works:

    • The query selects the publisher ID (pub_id) and calculates the average number of pages for books published by each publisher.

    • It groups the results by publisher ID using GROUP BY pub_id, ensuring that the average is calculated separately for each publisher.

    • Finally, the query returns the publisher ID and the corresponding average number of pages for each publisher.

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:


-- This SQL query calculates the average of distinct receive quantities from the purchase table.
SELECT AVG(DISTINCT(receive_qty)) -- Selects the average of distinct receive quantities from the 'purchase' table
FROM purchase; -- Specifies the table from which to retrieve data (purchase table)

Explanation:

  • This SQL query retrieves data from the purchase table.

  • It calculates the average of distinct receive quantities (receive_qty) from the purchase table.

  • The DISTINCT keyword ensures that duplicate values of receive_qty are eliminated before calculating the average.

  • Here's how the process works:

    • The query selects the distinct receive quantities from the purchase table using DISTINCT(receive_qty).

    • It then calculates the average of these distinct receive quantities using the AVG() function.

    • Finally, the query returns the calculated average of distinct receive quantities from the purchase table.

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:


-- This SQL query calculates the rounded average number of pages for each publisher's books.
SELECT pub_id, ROUND(AVG(no_page), 2) -- Selects the publisher ID and calculates the average number of pages for each publisher's books, rounding the result to 2 decimal places
FROM book_mast -- Specifies the table from which to retrieve data (book_mast table)
GROUP BY pub_id; -- Groups the results by publisher ID, so that the average number of pages is calculated for each publisher separately

Explanation:

  • This SQL query retrieves data from the book_mast table.

  • It calculates the average number of pages (AVG(no_page)) for books published by each publisher (pub_id).

  • The ROUND() function is used to round the average number of pages to 2 decimal places.

  • The GROUP BY clause ensures that the results are grouped by publisher ID, allowing for separate averages to be calculated for each publisher.

  • Here's how the process works:

    • The query selects the publisher ID (pub_id) and calculates the average number of pages for books published by each publisher.

    • It rounds the average number of pages to 2 decimal places using the ROUND() function.

    • The results are grouped by publisher ID using GROUP BY pub_id, ensuring that the average is calculated separately for each publisher.

    • Finally, the query returns the publisher ID and the rounded average number of pages for each publisher.

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:


-- This SQL query calculates the count of books and the average number of pages for each publisher.
SELECT pub_id, COUNT(pub_id), AVG(no_page) -- Selects the publisher ID, counts the number of books for each publisher, and calculates the average number of pages for each publisher's books
FROM book_mast -- Specifies the table from which to retrieve data (book_mast table)
GROUP BY pub_id; -- Groups the results by publisher ID, so that the count and average are calculated for each publisher separately

Explanation:

  • This SQL query retrieves data from the book_mast table.

  • It calculates the count of books (COUNT(pub_id)) and the average number of pages (AVG(no_page)) for books published by each publisher (pub_id).

  • The GROUP BY clause ensures that the results are grouped by publisher ID, allowing for separate counts and averages to be calculated for each publisher.

  • Here's how the process works:

    • The query selects the publisher ID (pub_id), counts the number of books for each publisher using COUNT(pub_id), and calculates the average number of pages for books published by each publisher using AVG(no_page).

    • The results are grouped by publisher ID using GROUP BY pub_id, ensuring that the count and average are calculated separately for each publisher.

    • Finally, the query returns the publisher ID, the count of books, and the average number of pages for each publisher.

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:


-- This SQL query calculates the average number of pages for books published by a specific publisher.
SELECT pub_id, AVG(no_page) -- Selects the publisher ID and calculates the average number of pages for books published by each publisher
FROM book_mast -- Specifies the table from which to retrieve data (book_mast table)
GROUP BY pub_id -- Groups the results by publisher ID, so that the average number of pages is calculated for each publisher separately
HAVING pub_id='P008'; -- Filters the results to only include data where the publisher ID is 'P008'

Explanation:

  • This SQL query retrieves data from the book_mast table.

  • It calculates the average number of pages (AVG(no_page)) for books published by each publisher (pub_id).

  • The GROUP BY clause ensures that the results are grouped by publisher ID, allowing for separate averages to be calculated for each publisher.

  • The HAVING clause filters the results to only include data where the publisher ID is 'P008'.

  • Here's how the process works:

    • The query selects the publisher ID (pub_id) and calculates the average number of pages for books published by each publisher.

    • It groups the results by publisher ID using GROUP BY pub_id, ensuring that the average is calculated separately for each publisher.

    • The HAVING clause then filters the results to only include data where the publisher ID is 'P008'.

    • Finally, the query returns the publisher ID and the average number of pages for books published by the specified publisher ('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.