**Summary**: in this tutorial, you will learn how to use the** MySQL aggregate functions **including `AVG`

, `COUNT`

, `SUM`

, `MAX `

and `MIN`

.

## Introduction to MySQL aggregate functions

The data that you need is not always stored in the tables. However, you can get it by performing the calculations of the stored data when you select it. For example, you cannot get the total amount of each order by simply querying from the `orderdetails`

table because the `orderdetails `

table stores only quantity and price of each item. You have to select the quantity and price of item for each order and calculate the order’s total. To perform such calculations in a query, you use aggregate functions.

By definition, an aggregate function performs a calculation on a set of values and returns a single value.

MySQL provides many aggregate functions including `AVG`

, `COUNT`

, `SUM`

, `MIN `

, `MAX`

, etc. An aggregate function ignores NULL values when it performs calculation except for the `COUNT `

function.

## AVG function

The `AVG `

function calculates the average value of a set of values. It ignores NULL values in the calculation.

1 | AVG(expression) |

You can use the `AVG `

function to calculate the average buy price of all products in the `products `

table by using the following query:

1 2 | SELECT AVG(buyPrice) average_buy_price FROM products |

The AVG function in detail.

## COUNT function

The `COUNT `

function returns the number of the rows in a table. For example, you can use the `COUNT `

function to get the number of products in the `products `

table as the following query:

1 2 | SELECT COUNT(*) AS Total FROM products |

The `COUNT `

function has several forms such as `COUNT(*)`

and `COUNT(DISTINCT expression)`

. For more information, check it out the COUNT function tutorial.

## SUM function

The `SUM `

function returns the sum of a set of values. The `SUM `

function ignores `NULL `

values. If no matching row found, the `SUM `

function return `NULL`

.

To get the total sales of each product, you can use the `SUM `

function in conjunction with the `GROUP BY`

clause as follows:

1 2 3 | SELECT productCode,sum(priceEach * quantityOrdered) total FROM orderdetails GROUP by productCode |

To see the result in more detail, you can join the `orderdetails `

table to the `products `

table as the following query:

1 2 3 4 5 6 7 | SELECT P.productCode, P.productName, SUM(priceEach * quantityOrdered) total FROM orderdetails O INNER JOIN products P ON O.productCode = P.productCode GROUP by productCode ORDER BY total |

1 |

More information on the SUM function in detail.

## MAX function

The `MAX `

function returns the maximum value in a set of values.

1 | MAX(expression) |

For example, you can use the MAX function to get the most expensive product in the `products `

table as the following query:

1 2 | SELECT MAX(buyPrice) highest_price, FROM Products |

How the MAX function works in detail.

## MIN function

The `MIN `

function returns the minimum value in a set of values.

1 | MIN(expression) |

For example, the following query uses the `MIN `

function to find the product with the lowest price in the `products `

table:

1 2 | SELECT MIN(buyPrice) lowest_price, FROM Products |

Explains the MIN function in detail.

In this tutorial, we have shown you how to use the most commonly used MySQL aggregate functions.