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

SQL aggregate functions calculate one result from a set of rows. An aggregate query can return an order count, total revenue, average payment, lowest price, or highest score. The five you'll use most are COUNT, SUM, AVG, MIN, and MAX. GROUP BY, HAVING, and NULL values control what they calculate.

This article covers the "Aggregations" chapter of Learn SQL, where you can run the queries against a real database.

SQL Aggregate Functions at a Glance

An aggregation is a single value that's derived by combining several other values. A query without GROUP BY treats all selected rows as one group, so an aggregate query normally returns one row.

Data stored in a database should generally be stored raw. When we need to calculate some additional data from the raw data, we can use an aggregation.

Take the following COUNT aggregation as an example:

SELECT COUNT(*)
FROM products
WHERE quantity = 0;

This query returns the number of products that have a quantity of 0. We could store a count of the products in a separate database table, and increment/decrement it whenever we make changes to the products table – but that would be redundant.

It's much simpler to store the products in a single place (we call this a single source of truth) and run an aggregation when we need to derive additional information from the raw data.

How Does COUNT Treat NULL Values?

COUNT counts rows or non-NULL values depending on its argument:

SELECT COUNT(*) AS order_count
FROM orders;

COUNT(*) counts rows. It includes rows containing NULL because it isn't inspecting a specific column.

SELECT COUNT(shipped_at) AS shipped_order_count
FROM orders;

COUNT(shipped_at) counts only rows where shipped_at isn't NULL. If 100 orders exist and 25 haven't shipped, COUNT(*) returns 100, while COUNT(shipped_at) returns 75.

You can also count unique non-NULL values:

SELECT COUNT(DISTINCT customer_id) AS customer_count
FROM orders;

Use COUNT(*) when you mean rows. Use COUNT(column) only when missing values should be excluded.

SUM and AVG for Numeric Data

The SUM aggregation function returns the sum of a set of values.

For example, the query below returns a single record containing a single field. The returned value is equal to the total salary being collected by all of the employees in the employees table.

SELECT SUM(salary)
FROM employees;

Which returns:

Just like we may want to find the minimum or maximum values within a dataset, sometimes we need to know the average! SQL offers us the AVG() function. Similar to MAX(), AVG() calculates the average of all non-NULL values.

SELECT AVG(song_length)
FROM songs;

This query returns the average song_length in the songs table.

Both functions ignore NULL inputs. That distinction matters for averages. If a column contains 10, 20, and NULL, then AVG returns 15, not 10. SQL divides by the two known values, not all three rows.

An average is only useful if the input represents the question you're asking. Filter canceled orders with WHERE before calculating average order value. Use COUNT(*) alongside AVG when you also need to know the sample size.

MIN and MAX Return Values, Not Rows

As you might expect, the MAX function retrieves the largest value from a set of values. For example:

SELECT MAX(price)
FROM products;

This query looks through all the rows in the products table and returns the largest price value. Remember, it only returns the price, not the rest of the record! You always need to specify each field you want a query to return.

The MIN function works the same as the MAX function but finds the lowest value instead of the highest value. Both ignore NULL inputs and work with sortable values such as numbers, dates, and text, although exact type support depends on the database.

Because these functions return values rather than complete rows, this query is misleading and invalid in standard SQL:

SELECT product_name, MIN(price)
FROM products;

MIN(price) produces one value, but several input rows may have different product_name values. Standard SQL doesn't define which name belongs in the result, and many databases reject the query. SQLite has a special rule for a single MIN() or MAX() aggregate: it takes bare columns from a row containing that minimum or maximum, so there the query returns the product_name and the price fields of the record with the lowest price. Ties are still ambiguous, so don't rely on that behavior in portable SQL.

Use a subquery when you need every product tied for the lowest price:

SELECT product_name, price
FROM products
WHERE price = (SELECT MIN(price) FROM products);

The subquery finds the minimum price; the outer query returns every matching row. The SQL subqueries guide covers this query shape and its multirow variants.

GROUP BY With Aggregate Functions

There are times when we need to group data based on specific values.

SQL offers the GROUP BY clause, which can group rows that have similar values into "summary" rows. It returns one row for each group. The interesting part is that each group can have an aggregation function applied to it that operates only on the grouped data.

Imagine that we have a database with songs and albums:

If we want to see how many songs are on each album, we can use a query like this:

SELECT album_id, COUNT(song_id) AS song_count
FROM songs
GROUP BY album_id;

This query retrieves a count of all the songs on each album. One record is returned per album, and they each have their own count:

In a grouped query, each selected expression generally needs to be either:

• Included in GROUP BY
• Passed to an aggregate function

This is valid because album_id identifies each group and COUNT(song_id) reduces that group's songs to one value. Selecting an unrelated column such as title would be ambiguous because an album can have many titles.

Filtering Groups With HAVING

When we need to filter the results of a GROUP BY query even further, we can use the HAVING clause. HAVING specifies a search condition for a group.

The HAVING clause is similar to the WHERE clause, but it operates on groups after they've been grouped, rather than rows before they've been grouped.

SELECT album_id, COUNT(id) AS count
FROM songs
GROUP BY album_id
HAVING COUNT(id) > 5;

This query returns the album_id and count of its songs, but only for albums with more than 5 songs.

It's common for developers to get confused about the difference between the HAVING and WHERE clauses – they're pretty similar after all.

The difference, though, is straightforward enough:

• A WHERE condition is applied to all the data in a query before it's grouped by a GROUP BY clause.
• A HAVING condition is applied only to the grouped rows that are returned after a GROUP BY is applied.

This means that if you want to filter based on the result of an aggregation, you need to use HAVING. If you want to filter on a value that's present in the raw data, you should use a simple WHERE clause.

Here's a query that uses both:

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) > 500;

The query runs in this order:

1. WHERE removes orders that aren't paid.
2. GROUP BY groups the remaining orders by customer.
3. SUM calculates each customer's total.
4. HAVING keeps totals greater than 500.

Filtering raw rows with WHERE also avoids sending irrelevant rows into the aggregation.

NULL Behavior in Aggregate Functions

COUNT(*) counts every input row. COUNT(column), SUM, AVG, MIN, and MAX ignore NULL inputs.

Empty input has another important rule. COUNT returns 0, but the other common aggregates return NULL when no rows qualify. A sum over no matching orders is unknown, not automatically zero.

Use COALESCE when your application needs a fallback:

SELECT COALESCE(SUM(amount), 0) AS total_revenue
FROM orders
WHERE status = 'refunded';

Decide what NULL means before adding a fallback. Replacing a missing average with zero falsely implies that observed values averaged to zero. Zero often makes sense for totals; NULL is usually more accurate for missing averages, minimums, and maximums.