Aggregate functions: COUNT, SUM, AVG
In short: Aggregate functions COUNT, SUM, AVG, MIN and MAX reduce many rows to a single value: a count, a total, an average, the smallest or the largest value.
Problem
The Monday report
Every Monday the owner asks the same things. How many customers do we have? How many cities are they from? How much money went through the shop? What is the most expensive product?
So far queries returned rows. Here you need one number from many rows. Counting by hand will not do: tomorrow there will be thousands of rows.
The idea
COUNT: how many
COUNT(*) counts the rows:
Twenty rows go in, one row comes out. A function that squeezes many rows into one value is called an aggregate function.
COUNT has two more forms. COUNT(column) counts only the rows where the column is not NULL. COUNT(DISTINCT column) counts the different values:
20 customers, 19 with a city (Boris has none) and 6 different cities.
Try it
How many have run out
The manager does not want the names, only the number: how many products have stock 0? The field shows which ones. Turn it into one number.
Solution
SELECT COUNT(*) FROM products WHERE stock = 0;SELECT COUNT(*) FROM products WHERE stock = 0. Two products. WHERE runs first and keeps the two rows, and COUNT(*) counts them.
The idea
SUM, AVG, MIN, MAX
The other four functions work with the values in the column:
SUM: the total;AVG: the average;MINandMAX: the smallest and the largest value.
The turnover is the sum of every order line: quantity times price.
1,963.50 €. Careful: this includes the lines of cancelled orders too. Which orders are cancelled is in orders, while the amounts are in order_items. We will bring the two tables together in the next module, with JOIN.
The cheapest, the most expensive and the average price of the products:
MIN and MAX work with text and dates too. The first and the last order:
Try it
The coffee's rating
The coffee beans (product_id = 17) got a bad review. How many reviews does it have, and what is its average rating?
Solution
SELECT COUNT(*), AVG(rating)
FROM reviews
WHERE product_id = 17;SELECT COUNT(*), AVG(rating) FROM reviews WHERE product_id = 17. Three reviews and an average of 3.33: two good ones (5 and 4) and a 1.
See it
One row, even when there is nothing
An aggregate without groups always returns one row. The three lines of order 8 become one:
And if not a single row passes WHERE? There are no reviews from October 2026 yet:
Still one row: COUNT gives 0, and AVG gives NULL. There is nothing to average. SUM, MIN and MAX do the same. In a report NULL looks like an empty cell, so when needed replace it with COALESCE(SUM(...), 0).
See it
AVG skips NULL
Like COUNT(column), SUM, AVG, MIN and MAX skip NULL too. With AVG this changes the result: you divide not by all the rows, but by those that have a value.
What is the average stock? And the average stock of the products we actually have? NULLIF(stock, 0) turns the zeros into NULL:
When you write AVG over a column with NULL, ask yourself: should the empty ones count as zero? If yes, AVG(COALESCE(column, 0)). If not, AVG(column) already does it.
Exercise
Two reports
1. The coffee supplier asks how many packs of coffee beans (product_id = 17) we have sold and for how much money.
The items and the money from the coffee beans:
Solution
SELECT SUM(quantity), SUM(quantity * unit_price)
FROM order_items
WHERE product_id = 17;SELECT SUM(quantity), SUM(quantity * unit_price) FROM order_items WHERE product_id = 17. 10 packs for 149 €. COUNT(*) here would give 3: that many lines, not items.
2. How many orders have been delivered, and to how many different customers?
The delivered orders and the different customers in them:
Solution
SELECT COUNT(*), COUNT(DISTINCT customer_id)
FROM orders
WHERE status = 'delivered';SELECT COUNT(*), COUNT(DISTINCT customer_id) FROM orders WHERE status = 'delivered'. 21 orders to 11 customers: some customers received more than one.
Test