How to Use the MySQL AVG() Function
In this tutorial, we'll learn how to use the AVG() function to calculate the average value of an expression in MySQL.
MySQL AVG() Syntax
The syntax of the AVG() function is as follows:
AGV([DISTINCT] expression)
Remarks
- The expression argument is required, and can be a column name or a formula.
- DISTINCT is optional. The DISTINCT option can be used to calculate the average of distinct values of an expression.
Examples
Below is the demo orders table:
order_id | customer_code | customer_name | order_number | order_date | amount | salesperson |
1 | #CID_0001 | Customer A | #ORD_1001 | 2019-02-01 | 1890.50 | Employee B |
2 | #CID_0002 | Customer B | #ORD_1002 | 2019-02-01 | 2300.00 | Employee B |
3 | #CID_0003 | Customer C | #ORD_1003 | 2019-02-01 | 1850.50 | Employee A |
4 | #CID_0004 | Customer D | #ORD_1004 | 2019-02-02 | 1240.50 | Employee C |
5 | #CID_0005 | Customer E | #ORD_1005 | 2019-02-02 | 2200.00 | Employee A |
6 | #CID_0006 | Customer F | #ORD_1006 | 2019-02-03 | 3200.80 | Employee C |
7 | #CID_0007 | Customer G | #ORD_1007 | 2019-02-04 | 1280.00 | Employee B |
8 | #CID_0008 | Customer H | #ORD_1008 | 2019-02-04 | 980.00 | Employee A |
Example 1: AVG()
The following statement returns the average of the amount column in the orders table:
SELECT AVG(o.amount) AS Average
FROM orders o;
Result:
1867.787500
Example 2: AVG() and ROUND()
The following statement returns the rounded average of the amount column in the orders table:
SELECT ROUND(AVG(o.amount), 2) AS RoundedAverage
FROM orders o;
Result:
1867.79
Example 3: AVG(), ROUND() and WHERE
The following statement returns the rounded average of the amount column between 2019-02-01 and 2019-02-03 in the orders table:
SELECT ROUND(AVG(o.amount), 2) RoundedAverage
FROM orders o
WHERE o.order_date BETWEEN "2019-02-01" AND "2019-02-03";
Result:
2113.72
Example 4: AVG() and GROUP BY
In this example, I show how to use the AVG() function with GROUP BY and ORDER BY.
The following statement returns a list of rounded average of the amount column between 2019-02-01 and 2019-02-10 by each customer_name, sorted the RoundedAverage result descending, from the orders table:
SELECT o.customer_name, ROUND(AVG(o.amount), 2) RoundedAverage
FROM orders o
WHERE o.order_date BETWEEN "2019-02-01" AND "2019-02-10"
GROUP BY o.customer_name
ORDER BY RoundedAverage DESC;
Result:
customer_name | RoundedAverage |
Customer F | 3200.8 |
Customer B | 2300 |
Customer E | 2200 |
Customer A | 1890.5 |
Customer C | 1850.5 |
Customer G | 1280 |
Customer D | 1240.5 |
Customer H | 980 |
In this tutorial, we've learned how to use the MySQL AVG() function to calculate the average value of an expression.