Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

MySQL AVG() function

AVG() function

MySQL AVG() function retrieves the average value of a given expression. If the function does not find a matching row, it returns NULL.

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

Contents:

Example : MySQL AVG() function

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

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

Sample Output: 

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

PHP script

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-avg-function- php MySQL examples | w3resource</title>
<meta name="description" content="example-avg-function- php MySQL examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>Showing number of pages in average from a collection of books:</h2>
<table class='table table-bordered'>
<tr>
<th>Average number of pages</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("MySQL:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT AVG(no_page)
FROM book_mast') as $row) {
echo "<tr>";
echo "<td>" . $row['AVG(no_page)'] . "</td>";
echo "</tr>";
}
?>
</table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

JSP script


<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<style type="text/css">
table {
    border-collapse: collapse;
}
table, th, td {
   border: 1px solid black;
}
</style>
<title>example-avg-function</title>
</head>
<body>
<%
try {
Class.forName("com.MySQL.jdbc.Driver").newInstance();
String Host = "jdbc:MySQL://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data = "SELECT AVG(no_page) FROM book_mast";
rs = statement.executeQuery(Data);
%>
<table>
<tr width="10" style="background-color:#FFFFCC">
<td>AVG(no_page)</td>
</tr>
<%
while (rs.next()) {
%>
<tr>
<td><%=rs.getString("AVG(no_page)")%></td>
</tr>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>

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.

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

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

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

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


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

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

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

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

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

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

Online Practice Editor:


Previous: Aggregate Functions and Grouping
Next: BIT_AND()