MySQL Aggregate Functions

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

MySQL Aggregate Functions - Order DetailsThe 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.

You can use the AVG function to calculate the average buy price of all products in the products table by using the following query:

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:

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:

To see the result in more detail, you can join the orderdetails table to the products table as the following query:

More information on the SUM function in detail.

MAX function

The MAX function returns the maximum value in a set of values.

For example, you can use the MAX function to get the most expensive product in the products table as the following query:

How the MAX function works in detail.

MIN function

The MIN function returns the minimum value in a set of values.

For example, the following query uses the MIN function to find the product with the lowest price in the products table:

Explains the MIN function in detail.

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