# 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.

Canonical: https://devsunite.com/resources/core-subjects/dbms/aggregate-functions
Last updated: 2026-11-22

Subject: DBMS · Category: SQL · 3 min read

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

1. **COUNT**: how many rows (or non-null values in a column).
2. **SUM**: the total of a numeric column.
3. **AVG**: the average of a numeric column.
4. **MIN**: the smallest value in a column.
5. **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:

```sql
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.

```sql
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:

```sql
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.

## Related

- [Previous: Other SQL Commands](https://devsunite.com/resources/core-subjects/dbms/other-sql-commands)
- [Next: SQL Joins](https://devsunite.com/resources/core-subjects/dbms/sql-joins)
- [More in SQL: Introduction to SQL](https://devsunite.com/resources/core-subjects/dbms/introduction-to-sql)
- [More in SQL: SQL Data Types](https://devsunite.com/resources/core-subjects/dbms/sql-datatypes)
- [More in SQL: Basic SQL Commands](https://devsunite.com/resources/core-subjects/dbms/basic-sql-commands)
- [More in SQL: SQL Views](https://devsunite.com/resources/core-subjects/dbms/sql-views)
- [More in SQL: SQL Indexes](https://devsunite.com/resources/core-subjects/dbms/sql-indexes)
- [More in SQL: SQL Subqueries](https://devsunite.com/resources/core-subjects/dbms/sql-subqueries)
- [More in SQL: SQL Stored Procedures](https://devsunite.com/resources/core-subjects/dbms/sql-stored-procedures)
- [All 174 DBMS interview questions](https://devsunite.com/resources/core-subjects/dbms)
