In MariaDB, the AVG() function returns the average value of the given expression.

The DISTINCT option can be used to return the average of the distinct values (i.e. remove duplicates before calculating the average).

NULL values are ignored.

Sample Data

Suppose we have a table with the following data:

SELECT 
    VendorId,
    ProductName,
    ProductPrice 
FROM Products;

Result:

+----------+---------------------------------+--------------+
| VendorId | ProductName                     | ProductPrice |
+----------+---------------------------------+--------------+
|     1001 | Left handed screwdriver         |        25.99 |
|     1001 | Right handed screwdriver        |        25.99 |
|     1001 | Long Weight (blue)              |        14.75 |
|     1001 | Long Weight (green)             |        11.99 |
|     1002 | Sledge Hammer                   |        33.49 |
|     1003 | Chainsaw                        |       245.00 |
|     1003 | Straw Dog Box                   |        55.99 |
|     1004 | Bottomless Coffee Mugs (4 Pack) |         9.99 |
+----------+---------------------------------+--------------+

Example

We can use the following query to get the average of all prices.

SELECT AVG(ProductPrice)
FROM Products;

Result:

52.898750

In this case, price information is stored in the ProductPrice column, and so we passed that as an argument to the AVG() function, which then calculated the average and returned the result.

Filtered Results

The AVG() function operates on the rows returned by the query. So if you filter the results, the result of AVG() will reflect that.

SELECT AVG(ProductPrice)
FROM Products
WHERE VendorId = 1001;

Result:

19.680000

In this case, 19.680000 is the average price of all the products offered by the specified vendor.

The DISTINCT Keyword

You can use the DISTINCT keyword with AVG() to calculate only distinct values. That is, if there are any duplicate values, they are treated as one value.

Example:

SELECT 
    AVG(ProductPrice) AS "All",
    AVG(DISTINCT ProductPrice) AS "Distinct"
FROM Products;

Result:

+-----------+-----------+
| All       | Distinct  |
+-----------+-----------+
| 52.898750 | 56.742857 |
+-----------+-----------+

In this case, two items share the same price (the left handed screwdriver and right handed screwdriver are both priced at 25.99). Therefore, the AVG() function, when used with the DISTINCT keyword, treats both of those values as one, and calculates its result accordingly.

Window Functions

The AVG() function can be used in window function. See SQL AVG() for Beginners for an example of this.