MySQL online course

MySQL Aggregate Functions: AVG, MIN, and MAX

Learn how MySQL aggregate functions summarize multiple rows with AVG(), MIN(), and MAX(), including filtering, NULL handling, aliases, and retrieving matching rows.

MySQL aggregate functions summarize values from multiple rows and return a result for the selected set. This lesson focuses on AVG(), MIN(), and MAX().

You should already understand basic SELECT statements, tables, rows, columns, numeric data types, and simple WHERE filtering. For background, review querying a database and MySQL data types.

What aggregate functions do

An aggregate function is a function that summarizes values from multiple rows into one result. The rows included in the calculation come from the query's source table and are limited by conditions such as WHERE.

A regular expression is normally evaluated row by row:

SELECT name, year + 1 AS next_year
FROM testtb;

This produces one calculated value for each selected row. An aggregate expression works across the selected values:

SELECT AVG(year) AS average_birth_year
FROM testtb;

When this query has no grouping, it normally returns one result row containing one summary value for the selected rows.

Example data set

Assume that testtb is a small people table with a person's name, surname, and birth year. The year column contains integer values.

SELECT * FROM testtb;

In the examples below, year is treated as a birth year. The same functions can summarize many other numeric columns, such as prices, quantities, or scores.

AVG(): calculate an arithmetic average

AVG() calculates the arithmetic mean of numeric values or numeric expressions. Its syntax is:

AVG(expression)

An expression can be a column reference, a calculation, or another SQL expression accepted by the function.

SELECT AVG(year) AS average_birth_year
FROM testtb;

The years add up to 9850. Dividing by five rows gives 1970, so MySQL can return a value such as 1970.0000, depending on the result type and client display.

Although every source value is an integer, an average does not have to be an integer. For example, averaging three values could produce 1991.3333. The average birth year is a numeric summary; it does not identify an actual person born in that year.

MIN(): find the smallest value

MIN() returns the smallest value among the evaluated values. Its syntax is:

MIN(expression)

SELECT MIN(year) AS earliest_birth_year
FROM testtb;

The result is 1921, the smallest value in the year column. When the values represent birth years, this is the oldest person's birth year.

MIN() returns only the minimum value. It does not automatically return the complete row or the associated person's name.

MAX(): find the largest value

MAX() returns the largest value among the evaluated values. Its syntax is:

MAX(expression)

SELECT MAX(year) AS latest_birth_year
FROM testtb;

The result is 1995, the largest value in the year column. When the values represent birth years, this is the youngest person's birth year.

Like MIN(), MAX() returns only the extreme value, not the row that owns it.

Comparing AVG(), MIN(), and MAX()

Reading aggregate query output

If you omit an alias, MySQL may label the output column with the expression itself:

SELECT AVG(year) FROM testtb;

The heading may appear as AVG(year). An alias is a readable output name assigned with AS:

SELECT AVG(year) AS average_birth_year,
       MIN(year) AS earliest_birth_year,
       MAX(year) AS latest_birth_year
FROM testtb;

Aliases change the result heading, not the stored column name. See column aliases for more naming techniques.

Filtering rows before aggregation

WHERE limits the source rows before AVG(), MIN(), or MAX() performs its calculation. This query averages only rows whose year is at least 1980:

SELECT AVG(year) AS average_recent_birth_year
FROM testtb
WHERE year >= 1980;

The included values are 1991, 1995, and 1988. Their average is approximately 1991.3333. The rows with years 1955 and 1921 do not participate in the calculation.

WHERE filters individual source rows. By contrast, HAVING filters groups after grouping and aggregation. For example, a grouped query could use HAVING to keep only groups whose average meets a condition. This is a related use of advanced SELECT statements.

NULL values and input types

NULL represents a missing or unknown value. AVG(), MIN(), and MAX() generally ignore NULL inputs rather than treating them as zero.

• If some rows contain NULL, those rows are excluded from the aggregate calculation.
• If the selected set is empty, or no non-NULL value remains, the result is NULL.
• AVG() requires numeric input suitable for arithmetic.
• MIN() and MAX() compare values according to the applicable data type and, for character data, collation rules.

For example, averaging text that contains names is not a meaningful numeric operation. Use a numeric column or clean and convert the data before calculating an average.

Aggregate values versus the rows that own them

Finding an extreme value and finding the row associated with that value are separate tasks. This query finds the earliest year:

SELECT MIN(year) AS earliest_birth_year
FROM testtb;

To retrieve the name and surname for every person with that year, use the aggregate in a subquery:

SELECT name, surname, year
FROM testtb
WHERE year = (SELECT MIN(year) FROM testtb);

The outer query returns row details, while the subquery calculates the minimum. The query also handles ties by returning every row whose year equals the minimum.

Grouping and ordinary columns

A query containing only aggregate expressions summarizes the selected rows as one result. Problems arise when ordinary columns are selected alongside an aggregate:

SELECT name, AVG(year)
FROM testtb;

There is no single unambiguous name for the average of all rows. Depending on the SQL mode and query form, MySQL may reject this query or produce nondeterministic semantics. Remove the unrelated column, use an appropriate GROUP BY, or use a separate query to retrieve associated rows.

GROUP BY partitions rows into groups and calculates an aggregate once per group. For example, if the table had a department column, a grouped query could calculate one average year for each department:

SELECT department, AVG(year) AS average_year
FROM testtb
GROUP BY department;

See MySQL functions for additional function categories and combining SELECT statements for related query techniques.

Troubleshooting common results

AVG() returns NULL

The filtered set may be empty, or every evaluated value may be NULL. Check the WHERE condition and inspect the source column for non-NULL numeric values.

AVG() fails or produces an unexpected result

The expression may not contain suitable numeric data. Use a numeric column, or clean and explicitly convert stored values before averaging.

MIN() or MAX() returns a value but not a person's name

This is expected: the aggregate returns the extreme value only. Use a subquery or an equivalent filter to retrieve matching rows, and account for multiple rows with the same extreme value.

The average has more decimal places than expected

MySQL can return decimal precision for an average even when the input column contains integers. Treat the result as a numeric average. Round or format it only when the presentation requirements call for that.

Exam-relevant summary

• An aggregate function processes values from multiple rows and returns a summary result.
• AVG(expression) returns the arithmetic average of numeric values.
• MIN(expression) returns the smallest evaluated value.
• MAX(expression) returns the largest evaluated value.
• WHERE filters rows before aggregation.
• Aggregate functions ignore NULL inputs; with no non-NULL input, the result is NULL.
• An aggregate returns a value, not automatically the complete row associated with that value.
• Use AS to give an aggregate result a readable alias.
• Use GROUP BY when the goal is one aggregate result per group.