MySQL Aggregate Functions
In this page:
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?
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
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
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
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
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: