SQL Aggregate Functions: A Complete Guide to COUNT, SUM, AVG, MIN, and MAX

What Are Aggregate Functions?

Aggregate functions perform a calculation on a set of rows and return a single value. They are the foundation of data summarization in SQL — powering everything from sales reports to analytics dashboards. The five core aggregate functions are COUNT, SUM, AVG, MIN, and MAX.

COUNT — How Many Rows?

COUNT returns the number of rows that match a condition.

-- Count all rows in the orders table
SELECT COUNT(*) FROM orders;

-- Count only rows where status is 'completed'
SELECT COUNT(*) FROM orders WHERE status = 'completed';

-- Count non-NULL values in a specific column
SELECT COUNT(discount_code) FROM orders;

Key point: COUNT(*) counts all rows including NULLs. COUNT(column_name) counts only non-NULL values in that column.

SUM — Add It All Up

SUM adds up numeric values in a column. NULL values are ignored.

-- Total revenue from all orders
SELECT SUM(total_amount) AS total_revenue FROM orders;

-- Total sales per product category
SELECT category, SUM(sale_price) AS category_revenue
FROM sales
GROUP BY category;

AVG — Find the Average

AVG calculates the arithmetic mean of a numeric column. Like SUM, it ignores NULL values.

-- Average order value
SELECT AVG(total_amount) AS avg_order_value FROM orders;

-- Average salary by department
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

Note: If you need to include NULLs as zero in an average, use AVG(COALESCE(column, 0)).

MIN and MAX — Extremes

MIN and MAX return the smallest and largest values respectively. They work on numbers, dates, and even strings (alphabetically).

-- Earliest and latest order dates
SELECT MIN(created_at) AS first_order, MAX(created_at) AS last_order
FROM orders;

-- Price range per category
SELECT category, MIN(price) AS cheapest, MAX(price) AS most_expensive
FROM products
GROUP BY category;

GROUP BY — Aggregating by Category

Aggregate functions become truly powerful when combined with GROUP BY. This clause groups rows with the same value in a specified column, then applies the aggregate to each group.

SELECT customer_id,
       COUNT(*) AS total_orders,
       SUM(total_amount) AS lifetime_value,
       AVG(total_amount) AS avg_order_value
FROM orders
GROUP BY customer_id;

HAVING — Filtering Aggregated Results

HAVING is like WHERE, but for aggregated results. You can't use WHERE to filter on the result of an aggregate function — that's what HAVING is for.

-- Find customers who have placed more than 5 orders
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;

-- Departments with average salary above a threshold
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 60000;

Common Mistakes to Avoid

Using WHERE instead of HAVING to filter on aggregate results — this causes an error.
Selecting non-aggregated columns without including them in GROUP BY — most databases will throw an error (MySQL in strict mode included).
Forgetting that AVG ignores NULLs — this can skew your averages if NULLs represent zeros.

Quick Reference

Function	Returns	Ignores NULLs?
COUNT(*)	Row count	No
COUNT(col)	Non-NULL count	Yes
SUM(col)	Total of values	Yes
AVG(col)	Mean of values	Yes
MIN(col)	Smallest value	Yes
MAX(col)	Largest value	Yes

Aggregate functions are among the most frequently used tools in any SQL developer's toolkit. Combine them with GROUP BY and HAVING and you can answer almost any summary question about your data.