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

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

1. Use COUNT(*) for row counts — it's the clearest way to count all rows
2. Use COALESCE with SUM/AVG — protect against NULL results when no rows match
3. Round AVG results — ROUND(AVG(salary), 2) gives cleaner output
4. 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.

Related Articles

• SQL HAVING Clause Explained: Filter Groups After Aggregation for the post-aggregation filter that usually sits right next to COUNT, SUM, and AVG.
• Mastering SQL GROUP BY: From Basics to Advanced Aggregations for the grouping layer that turns aggregate functions into category, customer, or department summaries.
• Mastering SQL DISTINCT: Remove Duplicates and Find Unique Values for the COUNT(DISTINCT ...) and uniqueness patterns that often appear next to aggregates.
• Handling NULLs in SQL: The Ultimate Guide for the NULL behavior that changes how COUNT, SUM, and AVG should be interpreted.

Now go ahead and try these functions in the interactive examples above. Experiment with different queries to solidify your understanding!