Lesson 11 of 26 · see the practice database

COUNT, SUM, AVG, MIN and MAX

Until now every query returned rows from the table. Often the question is about the table as a whole. How many customers are there? What is the total stock? What does the average product cost? Aggregate functions take many rows and boil them down to one value.

There are five to learn.

COUNT

SELECT COUNT(*) AS customers FROM customers;

COUNT(*) counts rows. The result is a single row with a single number, however large the table.

Add a WHERE and it counts only the rows that pass.

SELECT COUNT(*) AS delivered_orders
FROM orders
WHERE status = 'delivered';

Three kinds of count

SELECT COUNT(*) AS all_rows,
       COUNT(email) AS with_email,
       COUNT(DISTINCT city) AS different_cities
FROM customers;

• COUNT(*) counts every row.
• COUNT(column) counts the rows where that column is not NULL.
• COUNT(DISTINCT column) counts the different values.

Choosing the wrong one of these three is the most common counting mistake.

SUM

SUM adds up a number column.

SELECT SUM(stock) AS items_in_stock FROM products;

It can add up a calculation. This is the value of everything on the shelves.

SELECT SUM(price * stock) AS stock_value FROM products;

AVG

SELECT AVG(price) AS average_price FROM products;

Averages usually need rounding.

SELECT ROUND(AVG(salary)) AS average_salary FROM employees;

MIN and MAX

SELECT MIN(price) AS cheapest, MAX(price) AS dearest FROM products;

They work on text and dates as well. For text they give the first and last in alphabetical order. For dates, the earliest and the latest.

SELECT MIN(order_date) AS first_order, MAX(order_date) AS last_order
FROM orders;

Several at once

One query can return many summaries side by side.

SELECT COUNT(*) AS products,
       MIN(price) AS cheapest,
       MAX(price) AS dearest,
       ROUND(AVG(price), 2) AS average,
       SUM(stock) AS total_stock
FROM products
WHERE category = 'Electronics';

How aggregates treat NULL

All of them except COUNT(*) skip NULL. That is usually what you want, but it changes the meaning of an average. AVG divides by the number of rows that have a value, not by the number of rows.

SELECT COUNT(*) AS staff,
       COUNT(manager_id) AS with_manager,
       AVG(manager_id) AS avg_of_those_with_one
FROM employees;

If a missing value should count as zero, say so with COALESCE inside the function: AVG(COALESCE(bonus, 0)).

When there are no rows at all, COUNT gives 0 and the other four give NULL.

SELECT COUNT(*) AS how_many, SUM(price) AS total
FROM products
WHERE category = 'Toys';

The rule about mixing

This is the rule that matters most in this lesson. An aggregate gives one value for many rows. An ordinary column has a value on each row. They cannot sit side by side in a plain SELECT.

SELECT name, MAX(price) FROM products;

Which name should be shown beside the one maximum price? PostgreSQL, SQL Server and Oracle refuse this query with an error. SQLite runs it and, as a special favour, shows the name from the row that holds the maximum. MySQL, depending on its settings, either refuses or shows a name from some row or other. Because the behaviour is not dependable, do not write this.

Which product is the dearest?

So how do you get the name of the dearest product? Two honest ways. Sort and take one row, as in lesson 8:

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 1;

Or find the maximum first and then look for the rows that have it. The inner query in brackets is a subquery, and it has its own lesson later.

SELECT name, price
FROM products
WHERE price = (SELECT MAX(price) FROM products);

The second way has an advantage: if two products tie for the top price, it returns both.

Aggregates cannot go in WHERE

SELECT name FROM products WHERE price > AVG(price);

Error: misuse of aggregate function AVG()

WHERE tests one row at a time, before any summarising happens, so it cannot know the average. Again a subquery solves it.

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Whole-number averages

In SQLite, AVG always returns a decimal. In SQL Server, the average of a whole-number column is a whole number, with the fraction dropped. If your averages look suspiciously round there, multiply by 1.0 inside: AVG(salary * 1.0).

What comes next

A single total for the whole table is useful. A total for each category, or each city, or each month, is far more useful. That is GROUP BY, the next lesson, and it builds directly on the five functions you have just learnt.

Try it yourself

SELECT COUNT(*) FROM products;

SELECT MAX(salary), MIN(salary) FROM employees;

SELECT SUM(quantity) FROM order_items;

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.