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.

Leave a Reply

Your email address will not be published. Required fields are marked *