MySQL Aggregate Functions

Aggregate functions like COUNT, SUM, AVG, MIN, and MAX collapse many rows into a single summary value, and pair naturally with GROUP BY to compute that summary separately for each group.

In this page:

What are Aggregate Functions?
COUNT, SUM, and AVG at a Glance
MIN and MAX at a Glance
Aggregates with GROUP BY
When to Use an Aggregate Function

Syntax

sql

SELECT COUNT(*), SUM(column_name), AVG(column_name),
       MIN(column_name), MAX(column_name)
FROM table_name;

What are Aggregate Functions?

An aggregate function takes a set of rows as input and collapses them into a single summary value, such as a total count, sum, or average, rather than returning the individual rows themselves.

Example: What are Aggregate Functions?

sql

CREATE TABLE orders (id INT, total INT);
INSERT INTO orders VALUES (1, 50), (2, 30), (3, 70);
SELECT COUNT(*) AS order_count, SUM(total) AS total_sum FROM orders;

COUNT, SUM, and AVG at a Glance

COUNT returns how many rows match, SUM adds up the values in a numeric column, and AVG computes their mean -- these three are the most commonly used aggregate functions for summarizing numeric or countable data.

Example: COUNT, SUM, and AVG at a Glance

sql

CREATE TABLE orders (id INT, total INT);
INSERT INTO orders VALUES (1, 50), (2, 30), (3, 70);
SELECT COUNT(*) AS cnt, SUM(total) AS sum_total, AVG(total) AS avg_total FROM orders;

MIN and MAX at a Glance

MIN and MAX return the smallest and largest values found in a column across the matching rows, which is useful for finding extremes like the cheapest product or the largest order without manually scanning every row.

Example: MIN and MAX at a Glance

sql

CREATE TABLE products (id INT, price INT);
INSERT INTO products VALUES (1, 20), (2, 5), (3, 100);
SELECT MIN(price) AS cheapest, MAX(price) AS priciest FROM products;

Aggregates with GROUP BY

Aggregate functions are frequently combined with GROUP BY, which splits the result into separate buckets based on a column's value, so the aggregate is calculated independently for each group instead of across the entire table at once.

Example: Aggregates with GROUP BY

sql

CREATE TABLE orders (id INT, region TEXT, total INT);
INSERT INTO orders VALUES (1, 'North', 50), (2, 'South', 30), (3, 'North', 70);
SELECT region, SUM(total) AS region_total FROM orders GROUP BY region;

When to Use an Aggregate Function

Aggregate functions are the right tool whenever a query needs to answer a summary question like how many, how much, or what's the average, and they pair naturally with HAVING to filter groups based on their computed aggregate value.

Example: When to Use an Aggregate Function

sql

CREATE TABLE orders (id INT, region TEXT, total INT);
INSERT INTO orders VALUES (1, 'North', 50), (2, 'South', 30), (3, 'North', 70);
SELECT region, SUM(total) AS region_total
FROM orders
GROUP BY region
HAVING SUM(total) > 40;

Chapter Quiz — Complete all 7 topics to unlock

0/7 topics done

Complete these topics first:

← Previous Lesson MySQL UNION ALL Next Lesson → COUNT()