SQL Aggregate Functions
SQL aggregate functions take a set of rows and collapse them into a single summary value. The five common ones are COUNT, SUM, AVG, MIN and MAX. Used alone they summarize a whole table, and combined with GROUP BY they produce one summary value per group, such as a total per customer.
Aggregate functions take a set of rows and collapse them down into a single summary value: a total, a count, an average. They're how SQL answers questions like "how many," "what's the total," or "what's the average," rather than returning every individual row.
The five common aggregate functions
- COUNT: how many rows (or non-null values in a column).
- SUM: the total of a numeric column.
- AVG: the average of a numeric column.
- MIN: the smallest value in a column.
- MAX: the largest value in a column.
A worked example
An orders table with a handful of rows:
| order_id | customer_id | amount |
|---|---|---|
| 1 | 101 | 50 |
| 2 | 101 | 30 |
| 3 | 102 | 20 |
| 4 | 103 | 90 |
Without grouping, aggregates summarize the whole table:
SELECT COUNT(*), SUM(amount), AVG(amount), MAX(amount)
FROM orders;
-- returns: 4 | 190 | 47.5 | 90
Combining with GROUP BY
GROUP BY splits the rows into buckets first, then applies the aggregate function to each bucket separately, this is how you get a per-customer total instead of one grand total.
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;
Results:
| customer_id | order_count | total_spent |
|---|---|---|
| 101 | 2 | 80 |
| 102 | 1 | 20 |
| 103 | 1 | 90 |
Filtering groups with HAVING
WHERE filters individual rows before grouping happens; HAVING filters the groups themselves, after aggregation. To find only customers who've spent more than 50 total:
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 50;
-- returns only customer_id 101 (80) and 103 (90)
WHERE amount > 50 here would be wrong, it would filter out individual order rows before grouping, not filter which customers' totals exceed 50.
Where this matters in real systems
Nearly every dashboard, report, or analytics view in a real application is built on aggregate functions with GROUP BY: total revenue per day, average order value per region, number of signups per week. Understanding the WHERE-versus-HAVING distinction specifically is one of the most common real sources of subtly wrong reports, a filter placed in the wrong clause produces a query that runs successfully but returns the wrong numbers.
What interviewers actually ask about aggregate functions
- "Name the five common aggregate functions." COUNT, SUM, AVG, MIN, MAX.
- "What's the difference between WHERE and HAVING?" WHERE filters individual rows before grouping; HAVING filters groups after aggregation has already happened.
- "Why can't you use an aggregate function in a WHERE clause?" Because WHERE runs before aggregation exists, there's no aggregated value yet to filter on at that point, that's exactly what HAVING is for.
- "How would you find the customer with the highest total spend?" GROUP BY customer_id with SUM(amount), then ORDER BY that sum descending and LIMIT 1.
Key takeaways
- Aggregate functions (COUNT, SUM, AVG, MIN, MAX) collapse many rows into a single summary value.
- GROUP BY splits rows into buckets first, then the aggregate applies separately to each bucket.
- WHERE filters rows before grouping; HAVING filters the grouped results after aggregation.
- Most real dashboards and reports are built directly on GROUP BY plus aggregate functions.
More in SQL
Browse all DBMS topics
Log in to save which topics you've read across every subject.
Frequently asked questions
Name the five common aggregate functions.
COUNT, SUM, AVG, MIN, MAX.
What's the difference between WHERE and HAVING?
WHERE filters individual rows before grouping; HAVING filters groups after aggregation has already happened.
Why can't you use an aggregate function in a WHERE clause?
Because WHERE runs before aggregation exists, there's no aggregated value yet to filter on at that point, that's exactly what HAVING is for.
How would you find the customer with the highest total spend?
GROUP BY customer_id with SUM(amount), then ORDER BY that sum descending and LIMIT 1.