SQL Aggregate Functions: COUNT, SUM, AVG, MIN and MAX

SQL aggregate functions turn many rows into a smaller summary. They answer questions such as “How many orders do we have?” and “What is their average value?” The arithmetic is simple in our sample, which makes it easier to spot a misleading result.

Practice setup: These lessons use SQLite and the fictional shop database from the QueryFun SQL practice lab. Open that lab, run its setup once in a fresh practice database, then return here. Do not run the INSERT statements again for each lesson. You can use SQLite Fiddle without installing a database server.

The customers are Amina (1, Nairobi), Brian (2, Mombasa) and Chao (3, Nairobi). Orders are 101 (customer 1, amount 1200), 102 (customer 2, amount 800), 103 (customer 1, amount 600) and 104 (customer 3, amount 1500). Amounts are fictional currency units. Each order belongs to one customer. A customer can have several orders.

Build one summary row

Run this query against orders:

SELECT COUNT(*) AS order_count,
       SUM(amount) AS total_amount,
       AVG(amount) AS average_amount,
       MIN(amount) AS smallest_amount,
       MAX(amount) AS largest_amount
FROM orders;

The result is one row: 4 orders, a total of 4100, an average of 1025.0, a minimum of 600 and a maximum of 1500. COUNT(*) counts the input rows. SUM adds the amounts. AVG calculates the arithmetic mean. MIN and MAX return the smallest and largest values.

Always check the unit behind a summary. Here the count means orders, not customers, and the amount is in fictional currency units. A label such as total without its subject makes a result harder to interpret.

Check the arithmetic by hand

Our amounts are 1200, 800, 600 and 1500. Adding them gives 4100. Dividing by four orders gives 1025. This manual check matters because a valid query can still answer the wrong question. A database cannot decide whether you meant orders, customers or a filtered subset.

An average also hides differences. No order in the sample is worth exactly 1025. The average is a useful summary, but it is not a typical transaction you can point to in the table. Consider the minimum, maximum and count alongside it.

Apply WHERE before summarising

To summarise only orders worth at least 1000:

SELECT COUNT(*) AS order_count,
       SUM(amount) AS total_amount,
       AVG(amount) AS average_amount
FROM orders
WHERE amount >= 1000;

Orders 101 and 104 qualify. The result is count 2, total 2700 and average 1350.0. WHERE decides which source rows contribute to every aggregate in this query. The excluded orders do not contribute to the denominator of AVG.

Count orders and customers separately

Customer 1 placed two orders, so counting customer_id values in orders still counts four non-NULL entries. To count different ordering customers, use DISTINCT inside COUNT:

SELECT COUNT(*) AS orders,
       COUNT(DISTINCT customer_id) AS ordering_customers
FROM orders;

The answer is 4 orders and 3 ordering customers. If a customer has never ordered, this query cannot count them because it reads orders only. For the total registered customer count, query customers instead. The source table is part of the definition of your metric.

Know what happens with missing and empty inputs

COUNT(column) ignores NULL values. SUM, AVG, MIN and MAX also ignore NULL inputs. If amount contains 10, 20 and NULL, AVG(amount) is 15, not 10. That might be appropriate, but you should tell readers when information is missing.

If no rows meet a filter, COUNT(*) returns 0, while SQLite SUM(amount), AVG(amount), MIN(amount) and MAX(amount) return NULL. COALESCE(SUM(amount), 0) can display zero when that matches your reporting rule. It should not quietly turn an unknown measured amount into a known zero.

Avoid selecting an unrelated name alongside a total without a clear GROUP BY. SQLite accepts some such queries, but the name may not represent the summary in the way you expect. Keep this lesson's output entirely made of aggregates.

Try it yourself

Summarise only customer 1's orders. Return the number of orders, total, average, smallest and largest amount. Then change the customer number to 99 and explain the difference between the count and the other results.

SELECT COUNT(*) AS order_count,
       SUM(amount) AS total_amount,
       AVG(amount) AS average_amount,
       MIN(amount) AS smallest_amount,
       MAX(amount) AS largest_amount
FROM orders
WHERE customer_id = 1;

Keep learning: Return to the SQL learning path to choose your next lesson.

Further reading: SQLite SELECT documentation.