MySQL Aggregate Functions: SUM, COUNT, AVG & MAX

What Are Aggregate Functions in MySQL?

An aggregate function reads many rows of a single column and collapses them into one value. Aggregate functions are all about:

• Performing calculations on multiple rows
• Of a single column of a table
• And returning a single value.

The ISO standard defines five (5) aggregate functions, namely:

1. COUNT
2. SUM
3. AVG
4. MIN
5. MAX

One rule applies to all five: aggregate functions ignore NULL values. COUNT(*) is the single exception, and we look at why below.

Why Use Aggregate Functions

Different organization levels have different information requirements. Top level managers are usually interested in whole figures, not individual details.

Aggregate functions allow us to easily produce summarized data from our database.

For instance, from our myflix database, management may require the following reports:

• Least rented movies.
• Most rented movies.
• Average number of times that each movie is rented out in a month.

All of the above reports come from aggregate functions. Let’s look at each in detail.

COUNT function

The COUNT function returns the total number of values in the specified field, on both numeric and non-numeric data types. Like every aggregate function, COUNT(column) excludes NULL values.

COUNT(*) is a special form that returns the count of all rows in a table. It also counts NULLs and duplicates, because it counts rows rather than values.

The movierentals table holds this data:

Let’s suppose that we want to get the number of times that the movie with id 2 has been rented out.

SELECT COUNT(`movie_id`) FROM `movierentals` WHERE `movie_id` = 2;

Executing this in MySQL Workbench against myflixdb returns 3, because three rows carry movie_id 2.

DISTINCT Keyword

COUNT answers “how many”. The next question is usually “how many different ones”, and that is what DISTINCT is for.

The DISTINCT keyword omits duplicates from our results by grouping identical values together, exactly as the illustration above suggests.

First, let’s execute a simple query.

SELECT `movie_id` FROM `movierentals`;

Now the same query with the DISTINCT keyword:

SELECT DISTINCT `movie_id` FROM `movierentals`;

DISTINCT omits the duplicate records:

COUNT vs COUNT(*) vs COUNT(DISTINCT): Which One Should You Use?

DISTINCT can also be placed inside an aggregate function, and this is where most beginners lose track of which rows are actually counted. The four forms below all run against the same five-row movierentals table shown earlier, yet they do not all return the same number. The difference comes down to two questions: does the form count rows or values, and does it keep duplicates?

SELECT COUNT(*) AS `all_rows`,
       COUNT(`return_date`) AS `returned_rows`,
       COUNT(DISTINCT `movie_id`) AS `unique_movies`
FROM `movierentals`;

💡 Tip: Use COUNT(*) for a row count, COUNT(column) when a NULL should mean “does not apply”, and COUNT(DISTINCT column) for unique values. The opposite of DISTINCT is ALL — the default, and therefore rarely written out.

MIN function

The MIN function returns the smallest value in the specified table field.

Suppose we want the year in which the oldest movie in our library was released. MySQL’s MIN function gives us that.

SELECT MIN(`year_released`) FROM `movies`;

Result:

MAX function

Just as the name suggests, the MAX function is the opposite of the MIN function. It returns the largest value from the specified table field.

Suppose we want the year in which the latest movie in our database was released. The following example returns it.

SELECT MAX(`year_released`) FROM `movies`;

Result:

SUM function

MIN and MAX pick an existing value from a column. SUM and AVG compute a new number from the whole column.

Suppose we want the total amount of payments made so far. The MySQL SUM function returns the sum of all values in the specified column. SUM works on numeric fields only, and NULL values are excluded from the result.

The following table shows the data in the payments table.

The query shown below gets all the payments made and sums them up into a single result: 2500 + 2000 + 6000 = 10500.

SELECT SUM(`amount_paid`) FROM `payments`;

Result:

AVG function

The MySQL AVG function returns the average of the values in a specified column. Just like the SUM function, it works only on numeric data types.

Suppose we want to find the average amount paid. We can use the following query, which divides the total of 10500 by the three non-NULL payment rows.

SELECT AVG(`amount_paid`) FROM `payments`;

Result:

⚠️ Warning: AVG divides by the number of non-NULL rows, not by the row count of the table. A NULL amount is skipped rather than counted as zero, which quietly pushes the average up. Use AVG(IFNULL(`amount_paid`, 0)) when a missing value means zero.

Practical Example: Combining Aggregate Functions with GROUP BY

Each function above returned one figure for the whole table. Adding a GROUP BY clause returns one figure per group instead — and that is how real reports are built.

The following example groups members by name, then counts the total number of payments, the average payment amount, and the grand total of the payment amounts for each member.

SELECT m.`full_names`,
       COUNT(p.`payment_id`) AS `paymentscount`,
       AVG(p.`amount_paid`) AS `averagepaymentamount`,
       SUM(p.`amount_paid`) AS `totalpayments`
FROM members m, payments p
WHERE m.`membership_number` = p.`membership_number`
GROUP BY m.`full_names`;

Executing the above example in MySQL Workbench gives us the following results.

The query joins the two tables in the WHERE clause — the older comma-join style. Modern code writes the same logic as an explicit INNER JOIN … ON. Note also that every non-aggregated column in the SELECT list must appear in GROUP BY, or MySQL 5.7 and later reject the query under ONLY_FULL_GROUP_BY. See the official MySQL aggregate function reference.

FAQs