Ever wondered how databases can instantly tell you the total sales for the year, the average customer age, or how many orders were placed last month? The answer lies in SQL aggregate functions — powerful tools that transform rows of data into meaningful summaries.
In this guide, we'll explore the five essential aggregate functions: COUNT, SUM, AVG, MIN, and MAX. By the end, you'll know exactly when and how to use each one, including some gotchas that trip up even experienced developers.
What Are Aggregate Functions?
Aggregate functions perform calculations across multiple rows and return a single result. Unlike regular functions that work on individual values, aggregates "collapse" your data into summary statistics.
Here's a quick overview:
| Function | Purpose | Returns |
|---|---|---|
COUNT() | Counts rows or non-NULL values | Integer |
SUM() | Adds up numeric values | Number |
AVG() | Calculates the average | Number |
MIN() | Finds the smallest value | Same as input |
MAX() | Finds the largest value | Same as input |
COUNT: Counting Rows and Values
COUNT is probably the most used aggregate function. But did you know there are three different ways to use it?
COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)
-- Count ALL rows (including NULLs)
SELECT COUNT(*) FROM orders;
-- Count non-NULL values in a column
SELECT COUNT(shipping_date) FROM orders;
-- Count unique non-NULL values
SELECT COUNT(DISTINCT customer_id) FROM orders;
The key difference: COUNT(*) counts every row, while COUNT(column) only counts rows where that column is not NULL.
Try it yourself:
Notice how COUNT(bonus) returns 3 (only non-NULL values), while COUNT(*) returns 5 (all rows).
SUM: Adding Up Values
SUM adds up all the values in a numeric column. It automatically ignores NULL values.
SELECT SUM(amount) AS total_sales
FROM orders
WHERE order_date >= '2025-01-01';
Common pitfall: Summing a column with all NULL values returns NULL, not 0. Use COALESCE to handle this:
SELECT COALESCE(SUM(amount), 0) AS total_sales
FROM orders
WHERE order_date >= '2099-01-01'; -- No matching rows
AVG: Calculating Averages
AVG computes the arithmetic mean of a numeric column. Like SUM, it ignores NULL values — which can sometimes lead to unexpected results.
SELECT AVG(salary) AS avg_salary
FROM employees;
Important: AVG only considers non-NULL values when calculating. If you have 10 employees but only 8 have salary data, the average is based on those 8.
MIN and MAX: Finding Extremes
MIN and MAX work on any comparable data type — numbers, strings, dates, and more.
SELECT
MIN(order_date) AS first_order,
MAX(order_date) AS last_order,
MIN(amount) AS smallest_order,
MAX(amount) AS largest_order
FROM orders;
Pro tip: For strings, MIN returns the "alphabetically first" value and MAX returns the "alphabetically last" value.
Combining Aggregates with GROUP BY
Aggregate functions become even more powerful when combined with GROUP BY. Instead of one summary for the entire table, you get summaries for each group.
SELECT
department,
COUNT(*) AS employee_count,
AVG(salary) AS avg_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY department;
Common Mistakes to Avoid
1. Mixing Aggregates with Non-Aggregated Columns
This is a classic mistake:
-- ❌ WRONG: name is not aggregated or grouped
SELECT name, COUNT(*) FROM employees;
-- ✅ CORRECT: group by name
SELECT name, COUNT(*) FROM employees GROUP BY name;
2. Forgetting NULL Behavior
-- If all values are NULL, SUM returns NULL (not 0)
SELECT COALESCE(SUM(bonus), 0) AS total_bonus FROM employees;
3. Using AVG on Integer Columns
In some databases, AVG on integers returns an integer. Cast to avoid this:
SELECT AVG(CAST(rating AS REAL)) FROM reviews;
-- Or: AVG(rating * 1.0)
Best Practices
- Use COUNT(*) for row counts — it's the clearest way to count all rows
- Use COALESCE with SUM/AVG — protect against NULL results when no rows match
- Round AVG results —
ROUND(AVG(salary), 2)gives cleaner output - Combine with HAVING for filtered aggregates — filter after grouping
-- Find departments with average salary over 80000
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 80000;
Conclusion
SQL aggregate functions are essential tools for data analysis. Remember:
- COUNT counts rows or non-NULL values
- SUM adds up numbers (ignores NULLs)
- AVG calculates averages (ignores NULLs)
- MIN/MAX find extreme values (work on any comparable type)
The key gotcha is NULL handling — aggregates generally ignore NULLs, but this can lead to unexpected results if you're not careful. Always use COALESCE when you need a guaranteed non-NULL result.
Now go ahead and try these functions in the interactive examples above. Experiment with different queries to solidify your understanding!