Aggregate functions

Lesson 7 of 21 · Part 2 · Summarizing · about 15 minutes

So far every query has given back records, row by row. But many questions want a single number: how many products are there? How much stock in all? What does the most expensive one cost? Functions that turn many rows into one value are called aggregate functions.

main.sql

1SELECT COUNT(*) FROM products;

Output

COUNT(*)
10

1 row

The products table has 10 rows; the result is one row with one column: 10.

Counting: COUNT

COUNT(*) counts rows. COUNT(column) counts the cells in that column that are not NULL. There are 10 customers, but 3 of them left no email:

main.sql

1SELECT COUNT(*), COUNT(email) FROM customers;

Output

COUNT(*)	COUNT(email)
10	7

1 row

Same table, two different numbers: COUNT(*) is 10, COUNT(email) is 7. To know how many rows there are, use COUNT(*); to know how many cells of a column are filled in, use COUNT(column).

COUNT(DISTINCT column) counts only the different values. There are 12 orders; how many customers placed them?

main.sql

1SELECT COUNT(*), COUNT(DISTINCT customer_id) FROM orders;

Output

COUNT(*)	COUNT(DISTINCT customer_id)
12	7

1 row

12 orders from 7 customers: some customers ordered more than once.

Totals and averages: SUM, AVG

SUM adds up a column, and AVG gives its average:

main.sql

1SELECT SUM(stock), AVG(price) FROM products;

Output

SUM(stock)	AVG(price)
585	19.080000

1 row

All the products together have 585 in stock, and the average price is 19.08. MySQL shows the average as 19.080000, with a few extra zeros. Tidy it up with ROUND and AS from lesson 6:

main.sql

1SELECT SUM(stock) AS total_stock,2    ROUND(AVG(price), 2) AS avg_price3FROM products;

Output

total_stock	avg_price
585	19.08

1 row

The parentheses can hold a calculation, too. Each row of order_items is "how many × unit price"; add them all up and you have everything the shop has sold:

main.sql

1SELECT SUM(quantity * unit_price) AS sales FROM order_items;

Output

sales
433.70

1 row

Largest and smallest: MAX, MIN

MAX and MIN find the largest and smallest value. Numbers, dates and text all work:

main.sql

1SELECT MIN(price), MAX(price) FROM products;2SELECT MIN(order_date), MAX(order_date) FROM orders;

Output

Statement on line 1

MIN(price)	MAX(price)
4.50	59.00

1 row

Statement on line 2

MIN(order_date)	MAX(order_date)
2025-09-02	2025-10-25

1 row

The cheapest product is 4.50, the dearest 59.00; the first order was on 2025-09-02 and the last on 2025-10-25. An earlier date is "smaller".

Tip: Aggregate functions ignore NULL: SUM, AVG, MIN and MAX simply skip NULL cells, just as COUNT(column) doesn't count them.

WHERE first, then aggregate

Add a WHERE and only the rows it keeps are counted. How many electronics products are there, and how much stock do they have in all?

main.sql

1SELECT COUNT(*), SUM(stock)2FROM products3WHERE category = 'Electronics';

Output

COUNT(*)	SUM(stock)
4	245

1 row

Press Step and watch: WHERE first decides row by row what stays, leaving 4 electronics products; only then are those 4 rows turned into one.

And if no row is left?

main.sql

1SELECT COUNT(*), SUM(stock)2FROM products3WHERE price > 100;

Output

COUNT(*)	SUM(stock)
0	NULL

1 row

No product costs more than 100. COUNT(*) is 0, but SUM is NULL: with nothing to add up, there is no total.

Guess first

London has 3 customers, and Chloe Martin left no email. What is the result?

1SELECT COUNT(email)2FROM customers3WHERE city = 'London';

WHERE keeps London's 3 customers, then COUNT(email) counts the emails that are not NULL. Chloe's is NULL, so it doesn't count: the answer is 2. To count people, use COUNT(*).

Many rows become one

An aggregate function turns many rows into one. That's why it can't sit next to an ordinary column:

main.sql

1SELECT name, MAX(price) FROM products;

Output

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'town.products.name'; this is incompatible with sql_mode=only_full_group_by

MAX(price) is one value, but there are 10 names: which one should it show? MySQL won't guess for you and gives ERROR 1140. To find which product is the most expensive, lesson 4 already has the answer: ORDER BY price DESC LIMIT 1. In the next lesson, GROUP BY makes aggregates give not just one row but one row per group.

Practice

Write the query, then press Check. A hint costs nothing; the answer is there when you need it.

Exercise 1

Prices at a glance

Get the lowest, highest and average product price, in columns named lowest, highest and average, with the average rounded to two decimal places.

It should print

lowest	highest	average
4.50	59.00	19.08

1 row

exercise1.sql

1SELECT price FROM products;

Output

Press Run or Ctrl+Enter

Exercise 2

Order 3

From order_items, work out how many items order 3 has in all (column items) and what it costs in total (column total).

It should print

items	total
3	76.00

1 row

exercise2.sql

1SELECT * FROM order_items;

Output

Press Run or Ctrl+Enter

Exercise 3

Fix it: counting people

This query should count the customers in Paris, but it says 1, and Paris has 2 customers. Fix it (keep the column name COUNT(*)).

It should print

COUNT(*)
2

1 row

exercise3.sql

1SELECT COUNT(email) FROM customers WHERE city = 'Paris';

Output

Press Run or Ctrl+Enter

What you learned

Aggregate functions turn many rows into one value: COUNT, SUM, AVG, MIN, MAX.
COUNT(*) counts rows; COUNT(column) skips NULL; COUNT(DISTINCT column) counts only different values.
SUM, AVG, MIN and MAX skip NULL; with no rows at all, COUNT(*) is 0 and SUM is NULL.
The parentheses can hold a calculation, such as SUM(quantity * unit_price).
WHERE picks the rows first; the aggregate function works on what is left.
Without grouping, an aggregate can't sit next to an ordinary column (ERROR 1140).