Aggregate Functions (COUNT, SUM, AVG, MIN, MAX) in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this SELECT and Column Expressions, FROM and Table References, NULL Semantics and IS NULL
Builds toward GROUP BY, Scalar Subqueries, NULL Handling in Joins and Aggregates, Aggregate Window Functions (SUM, AVG, COUNT OVER)
What are Aggregate Functions in SQL?
Aggregate functions collapse a set of rows down to a single number.
You're in the database building a summary for the finance team. How many orders came in this month? What's total revenue? What's the average order value? These aren't questions you scroll through and count manually. The orders table might have hundreds of thousands of rows. You write one query and SQL does the counting and summing across the whole table at once.
What do COUNT, SUM, AVG, MIN and MAX do in SQL?
The five functions that handle this are COUNT, SUM, AVG, MIN, and MAX. Each one scans a column across every row and returns a single result:
SELECT COUNT(*) AS order_count,
SUM(total_amount) AS total_revenue,
AVG(total_amount) AS avg_order_value
FROM ordersOne query, one row, three summary numbers. No matter how many rows the table has, a bare aggregate query always gives you one row back. MIN and MAX work the same way, returning the lowest or highest value in a column:
SELECT MIN(price) AS cheapest, MAX(price) AS most_expensive
FROM productsHow do you aggregate only some rows with WHERE?
You can narrow any aggregate to a specific subset with WHERE. SQL filters the rows first, then runs the aggregate on whatever survives:
SELECT AVG(total_amount) AS avg_delivered_value
FROM orders
WHERE status = 'delivered'Only delivered orders feed into the average. Every other row is gone before the aggregation runs. This is how you build conditional summaries: revenue for one region, count for one product category, average for one customer segment.
What is the difference between COUNT(*) and COUNT(column)?
The one thing that trips people up: COUNT(*) and COUNT(column_name) look nearly identical but can return very different numbers.
COUNT(*) counts rows. Every row, regardless of what any column contains. COUNT(city) counts only rows where city is not NULL. On a customers table with 70 records where 9 are missing a city value, the two forms diverge:
SELECT COUNT(*) AS total_customers, COUNT(city) AS customers_with_city FROM customers
The gap is your data quality signal. Any time you're auditing whether a column is fully populated, this pattern shows you immediately how many rows are missing a value.
Do SUM and AVG ignore NULL values in SQL?
The NULL-skipping logic extends across the whole family. SUM, AVG, MIN, and MAX all skip NULL values silently. If every row matching your filter has NULL in the target column, you get NULL back, not zero. That matters when the result feeds into a report that expects a number.
One more form worth knowing: COUNT(DISTINCT column) counts unique non-NULL values. If 200 orders came from 62 distinct customers, COUNT(DISTINCT customer_id) returns 62, not 200.
A table has 100 rows. 10 rows have NULL in the `city` column. What does COUNT(city) return?
Practice Aggregate Functions in SQL
Brightlane's operations team is building a weekly capacity report and needs a baseline order-volume figure.
Write a query to return the total number of orders the platform has processed in a single column named order_count.
Assumptions:
- The
orderstable contains every order Brightlane has processed. - Every row in
ordersrepresents one order; the count is simply the row count.
Output:
- A single row with one column,
order_count, containing the total order count.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution10 Aggregate Functions practice problems
Write a query to return the total number of orders the platform has processed in a single column named order_count.
Write a query to return the summed list price as a single figure named total_catalog_value.
Write a query to return the lowest and highest product prices in a single row.
Write a query to return all three in a single row.
Write a query to return the average order value for delivered orders in a single column named avg_delivered_value.
Write a query to return the number of unique customers who appear in the orders history, in a single column named unique_customers.
Write a query to return the total unit count in a single column named total_units.
Write a query to return both counts in one row.
Write a query to return the exact average in a single column named avg_quantity.
Write a query to return the total list price of all products currently assigned to category 999, in a single column named total_price.
Start learning to practice all 10 Aggregate Functions problems, with instant grading and mastery tracking.
Deeper guides on Aggregate Functions
- the aggregate that returns text
string_agg needs a delimiter, and the one-argument call fails with an error about the function not existing.
- why COUNT(*) and COUNT(column) disagree
Most skip rows whose input is null. COUNT(*), array_agg and jsonb_agg do not, so two counts disagree.
Common questions about Aggregate Functions
What does SUM return when every value is NULL?
NULL, not zero. Aggregates skip missing values, so summing a column where nothing is recorded leaves nothing to add and the answer is unknown rather than none. Wrap it in COALESCE when a report needs a number in that cell.
Does an aggregate query return a row when nothing matches?
Yes, one row. A bare aggregate with no GROUP BY always produces exactly one row, so a filter that matches nothing gives you a count of zero beside a NULL sum rather than an empty result. The presence of a row is not evidence that any data was found.
Can one query use several different aggregates at once?
Yes, and it is the normal way to build a summary. Listing a count, a total, an average and the extremes side by side reads the table once and hands back a single row holding all of them.
What does COUNT DISTINCT actually count?
Unique values that are not NULL. Given four rows holding one, one, two and a missing value, it returns two. That is different from counting the column, which returns three, and from counting every row, which returns four.
