SQL Aggregate Functions Explained (COUNT, SUM, AVG)

SQL aggregate functions are built-in tools that take many rows of data and turn them into one summary number. The five you will use almost every day are COUNT(), SUM(), AVG(), MIN(), and MAX(). For example, COUNT() tells you how many rows match, and SUM() adds up a column of numbers. In this guide you will learn what each one does, with small, copy-pasteable examples you can run yourself.

What Is an Aggregate Function in SQL?

A table in a database is just rows and columns, like a spreadsheet. Normally, a SELECT query returns one row of output for every row that matches. An aggregate function is different: it looks at a whole column (or a whole group of rows) and returns a single value that summarizes them, such as a total, an average, or a count.

Think of a spreadsheet of orders. Instead of scrolling through 5,000 rows to find the total sales, you can ask the database to add them up for you in one line of SQL.

The 5 Most Common Aggregate Functions

We will use one sample table called orders for every example below. It has three columns: id (a unique number for each order), customer (the customer's name), and amount (how much the order cost, in dollars).

COUNT() – Counting Rows

COUNT() answers the question "how many?". It is the aggregate function beginners reach for most often, because almost every report starts with a total number of something.

SELECT COUNT(*) AS total_orders
FROM orders;

Line by line: SELECT COUNT(*) tells the database to count every row, no matter what is in it. AS total_orders just renames the result column so the output is easy to read (without it, the column would be called count, which is fine too). FROM orders says which table to look in. The result is a single row with one number, like 248.

You can also count a specific column instead of every row. COUNT(customer) counts only the rows where customer is not empty (in SQL, an empty or unknown value is called NULL). COUNT(*) counts all rows, including ones with NULL values; COUNT(column_name) skips NULLs in that column.

SUM() – Adding Up Numbers

SUM() adds every value in a numeric column together and returns one total. It only works on numbers, not on text.

SELECT SUM(amount) AS total_revenue
FROM orders;

This adds up every value in the amount column across all rows in the orders table and labels the single result total_revenue. If your orders table has rows of 20, 35, and 45, this query returns 100. Rows where amount is NULL are treated as if they were not there, so they do not lower the total.

AVG() – Finding the Average

AVG() calculates the mean of a column: it adds up all the values, then divides by how many values there are (ignoring NULLs).

SELECT AVG(amount) AS average_order_value
FROM orders;

AVG(amount) computes the average of every value in the amount column. For orders of 20, 35, and 45, AVG returns 33.33 (100 divided by 3). This is useful for questions like "what is our average order value?" without you having to calculate it by hand.

MIN() and MAX() – Smallest and Largest Values

MIN() returns the smallest value in a column, and MAX() returns the largest. They work on numbers, dates, and even text (alphabetically), which makes them useful far beyond just prices.

SELECT MIN(amount) AS cheapest_order,
       MAX(amount) AS most_expensive_order
FROM orders;

This query runs two aggregate functions in the same SELECT statement, separated by a comma. MIN(amount) finds the lowest value in the amount column, and MAX(amount) finds the highest. The result is one row with two columns, showing both numbers side by side.

Combining Aggregate Functions with GROUP BY

So far every example summarized the entire table into one number. GROUP BY lets you get one summary number per group instead — for example, total spend per customer rather than one grand total for everyone.

SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer;

GROUP BY customer tells the database to split the orders table into one group per unique customer name, before running SUM(amount) inside each group. The result is one row per customer, showing how much that customer spent in total. Any column you SELECT alongside an aggregate function (like customer here) must also appear in the GROUP BY clause.

Filtering Aggregated Results with HAVING

WHERE filters rows before they are grouped. But if you want to filter after the aggregate function has run — for example, only show customers who spent more than $100 in total — you need HAVING instead.

SELECT customer, SUM(amount) AS total_spent
FROM orders
GROUP BY customer
HAVING SUM(amount) > 100;

This runs the same grouped SUM as before, but HAVING SUM(amount) > 100 removes any customer group whose total is 100 or less from the final result. You cannot write WHERE SUM(amount) > 100 here, because WHERE runs before grouping happens, and the total does not exist yet at that point.

Common Mistakes Beginners Make

• Using WHERE to filter an aggregate result — use HAVING instead.
• Forgetting to add non-aggregated columns to GROUP BY, which causes an error in most databases.
• Assuming SUM() or AVG() include NULL values as zero — they simply skip them.
• Mixing up COUNT(*) (all rows) with COUNT(column) (only non-NULL values in that column).
• Trying to run an aggregate function on a text column with SUM() or AVG() — those two only work on numbers.