MySQL AVG() Function with Examples

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_idcustomer_codecustomer_nameorder_numberorder_dateamountsalesperson
1#CID_0001Customer A#ORD_10012019-02-011890.50Employee B
2#CID_0002Customer B#ORD_10022019-02-012300.00Employee B
3#CID_0003Customer C#ORD_10032019-02-011850.50Employee A
4#CID_0004Customer D#ORD_10042019-02-021240.50Employee C
5#CID_0005Customer E#ORD_10052019-02-022200.00Employee A
6#CID_0006Customer F#ORD_10062019-02-033200.80Employee C
7#CID_0007Customer G#ORD_10072019-02-041280.00Employee B
8#CID_0008Customer H#ORD_10082019-02-04980.00Employee 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_nameRoundedAverage
Customer F3200.8
Customer B2300
Customer E2200
Customer A1890.5
Customer C1850.5
Customer G1280
Customer D1240.5
Customer H980

In this tutorial, we've learned how to use the MySQL AVG() function to calculate the average value of an expression.


See also:
MySQL LIKE Operator Pattern Matching and Examples
MySQL SUBSTRING_INDEX Function with Examples
MySQL EXISTS Operator with Examples
MySQL ROW_NUMBER Function with Examples
MySQL CONCAT() Function | Concatenate Strings in MySQL

Leave a Comment