VMware ESXi and vSphere Cluster Management

MySQL Aggregate Functions: AVG, MIN, and MAX

Learn how MySQL aggregate functions summarize values across rows using AVG(), MIN(), and MAX(), with practical birth-year examples.

What aggregate functions do

An aggregate function is a SQL function that combines values from multiple rows into one summarized result. Aggregate functions are commonly applied to a column or an expression, which is a value or calculation supplied to a SQL function.

A normal SELECT query can return one row for every matching record. For example:

SELECT name, surname, year
FROM testtb;

This query returns the individual people stored in the table. An aggregate query instead summarizes values, so it can return one result for the entire set of matching rows.

Sample data set

Use a table named testtb with a name column, a surname column, and a year column. In these examples, year stores each person's birth year.

Inspect the records before summarizing them:

SELECT * FROM testtb;

The result set is the collection of rows and columns returned by a query. The query above returns all five people, while the aggregate queries below return one summarized row because they do not use grouping.

AVG(): calculate an arithmetic average

AVG() returns the arithmetic average, or mean, of numeric values. MySQL adds the selected values and divides the total by the number of values included.

SELECT AVG(year) FROM testtb;

For the sample data, the calculation is:

(1979 + 1988 + 1995 + 2001 + 2010) / 5 = 1994.6

The result represents the average birth year of all people in the table:

The exact displayed precision can depend on MySQL's result formatting and the data type. A decimal result is expected even though every source year is an integer, because an arithmetic average does not have to be a whole number.

MIN(): find the lowest value

MIN() returns the lowest value in the selected set.

SELECT MIN(year) FROM testtb;

The smallest birth year is 1979. Because these values represent birth years, the earliest year identifies the oldest birth year represented in the sample. The meaning of “lowest” depends on the data: for a price column, for example, it would mean the smallest price.

MAX(): find the highest value

MAX() returns the highest value in the selected set.

SELECT MAX(year) FROM testtb;

The largest birth year is 2010. Since a later birth year generally represents a younger person, this identifies the youngest birth year in the sample.

Reading aggregate query output

Each query returns a single-row result because the query applies one aggregate function to all qualifying rows and does not request groups.

When an aggregate expression has no alias, MySQL commonly uses the expression itself as the output label. Therefore, the labels appear as AVG(year), MIN(year), and MAX(year). The output label describes the calculation, while the value is the summary produced from the table.

Common points of confusion

Why does the query return only one row?

This is expected. Without grouping, an aggregate function summarizes all rows that qualify for the query into one result. A normal column selection returns individual values, while an aggregate selection returns a summary.

Why is AVG() a decimal?

The average may fall between two whole numbers. MySQL can therefore return fractional precision even when the source column contains only integers.

Does MIN(year) always mean the oldest person?

No. It means the lowest numeric year. It identifies the oldest birth year only because this column represents birth years, where an earlier year means an older person.

Does MAX(year) always mean the youngest person?

No. It means the highest numeric year. It identifies the youngest birth year here because a later birth year generally means a younger person.

Key points

• Aggregate functions combine values from multiple rows into one result.
• AVG(year) calculates the average numeric value and may return decimals.
• MIN(year) returns the lowest value in the selected set.
• MAX(year) returns the highest value in the selected set.
• Without grouping, each example produces a single-row result set.
• The interpretation of a result depends on what the column represents.