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 Tree:
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.0">
<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>
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 Tree:
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
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 Tree:
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
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 Tree:
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 Tree:
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()
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises