Introduction
Aggregate functions take a collection of rows and reduce them to a single summary value. They are the backbone of reporting queries: total revenue, average order value, number of customers, and so on. The five core aggregates in standard SQL are COUNT, SUM, AVG, MIN, and MAX, and they can be used on their own or combined with GROUP BY to summarize data per category.
Cricket analogy: A season's aggregate functions are like summarizing a batsman's stats: COUNT gives innings played, SUM gives total runs, AVG gives batting average, MIN/MAX give lowest and highest scores, and GROUP BY breaks it down per opponent team like Virat Kohli's stats against Australia versus England.
Syntax
SELECT COUNT(*) AS total_rows,
COUNT(column_name) AS non_null_count,
SUM(column_name) AS total,
AVG(column_name) AS average,
MIN(column_name) AS smallest,
MAX(column_name) AS largest
FROM table_name
WHERE condition;Explanation
COUNT(*) counts every row in the result set regardless of NULLs, while COUNT(column_name) counts only rows where that column is not NULL. SUM and AVG ignore NULL values entirely when computing their result; they do not treat NULL as zero. MIN and MAX also skip NULLs and return the smallest or largest non-NULL value. If every value in the column is NULL, SUM, AVG, MIN, and MAX all return NULL, while COUNT(column_name) returns 0.
Cricket analogy: COUNT(*) counts every ball bowled in an over including wides, while COUNT(runs_scored) only counts balls where a run value was actually recorded; SUM and AVG of runs ignore any ball with no recorded value rather than treating it as a zero-run dot ball.
Example
-- orders table
-- order_id | customer_id | amount
-- 1 | 101 | 250
-- 2 | 102 | NULL
-- 3 | 101 | 100
-- 4 | 103 | 400
SELECT COUNT(*) AS total_orders,
COUNT(amount) AS priced_orders,
SUM(amount) AS total_revenue,
AVG(amount) AS avg_order_value,
MIN(amount) AS smallest_order,
MAX(amount) AS largest_order
FROM orders;Output
total_orders = 4 (counts all rows including the NULL amount), priced_orders = 3 (skips the NULL), total_revenue = 750, avg_order_value = 250 (750 divided by 3, not 4), smallest_order = 100, largest_order = 400. Notice the average is computed over the 3 non-NULL rows only, which is a common source of confusion for beginners.
Cricket analogy: Like a batting card with 4 balls faced but only 3 having a recorded shot outcome, COUNT(*) reads 4, COUNT(shot) reads 3, total runs off those 3 shots come to 750, and the average of 250 is computed only over the 3 valid deliveries, not all 4.
Key Takeaways
- COUNT(*) counts all rows; COUNT(column) counts only non-NULL values in that column.
- SUM, AVG, MIN, and MAX ignore NULLs rather than treating them as zero.
- If a column has zero non-NULL values, SUM/AVG/MIN/MAX return NULL, but COUNT(column) returns 0.
- Aggregate functions collapse many rows into one value unless combined with GROUP BY.
- Use DISTINCT inside an aggregate, e.g. COUNT(DISTINCT customer_id), to summarize unique values.
Practice what you learned
1. Given a column with values (10, NULL, 20, NULL), what does AVG(column) return?
2. What is the difference between COUNT(*) and COUNT(column_name)?
3. If a table has 5 rows and a column is entirely NULL, what does SUM(that_column) return?
4. Which function would you use to count the number of unique customer_id values in an orders table?
Was this page helpful?
You May Also Like
GROUP BY and HAVING
Understand how GROUP BY partitions rows into groups and how HAVING filters those groups after aggregation, unlike WHERE.
DISTINCT and Basic Operators
Remove duplicate rows with DISTINCT and use comparison, logical, and pattern-matching operators in queries.
Window Functions
Functions that compute values across a set of related rows without collapsing them into a single output row, using OVER().