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

Every query you have written so far retrieves individual rows from the database. You ask for products, and you get back a list of products. You ask for orders, and you see each order on its own row. But what if you need answers that span across multiple rows? How many products do you have in total? What is the average order value? What was your highest single sale?

These questions cannot be answered by looking at individual rows. They require the database to analyze a group of rows and return a single summarized result. This is called aggregation, and SQL provides five core functions for it: COUNT, SUM, AVG, MIN, and MAX.

Aggregate functions are the foundation of every report, dashboard, and analytics query you will ever write. This guide covers each function in depth, explains how they handle NULL values (a topic that causes subtle bugs), clarifies the critical difference between COUNT(*) and COUNT(column), and walks you through practical examples using the ShopSmart sample database (we defined it in a previous guide here).

What Is Aggregation?

Aggregation is the process of taking multiple rows of data and condensing them into a single result. Instead of returning individual rows, an aggregate function processes all matching rows and returns one summarized value.

Think of it like this: if a regular SELECT query is reading every line of a spreadsheet, aggregation is using the summary row at the bottom that shows the total, the average, or the count.

Here is the fundamental difference:

-- Non-aggregated: Returns one row PER product (10 rows)
SELECT name, price
FROM products;

-- Aggregated: Returns ONE row with a single summary value
SELECT AVG(price) AS average_price
FROM products;

The first query returns 10 rows. The second processes all 10 prices and returns a single number: the average.

Output of the aggregated query:

That single number represents the average price across all 10 products. This is aggregation in action.

COUNT: Counting Rows

COUNT is the most frequently used aggregate function. It returns the number of rows that match your query.

COUNT(*): Count All Rows

COUNT(*) counts every row in the result set, regardless of what values the columns contain:

SELECT COUNT(*) AS total_products
FROM products;

Output:

There are 10 rows in the products table, so COUNT(*) returns 10.

SELECT COUNT(*) AS total_orders
FROM orders;

Output:

SELECT COUNT(*) AS total_customers
FROM customers;

Output:

COUNT(*) with WHERE

COUNT(*) respects the WHERE clause, counting only rows that pass the filter:

-- How many products are available?
SELECT COUNT(*) AS available_products
FROM products
WHERE is_available = true;

Output:

-- How many orders are pending?
SELECT COUNT(*) AS pending_orders
FROM orders
WHERE status = 'pending';

Output:

-- How many customers signed up in 2024?
SELECT COUNT(*) AS new_customers_2024
FROM customers
WHERE signup_date >= '2024-01-01';

Output:

COUNT(column): Count Non-NULL Values

COUNT(column) counts only the rows where the specified column is NOT NULL. This is a critical distinction from COUNT(*).

Recall that our customers table has a customer (Grace Taylor) with a NULL city:

-- COUNT(*) counts ALL rows, including those with NULL city
SELECT COUNT(*) AS total_rows
FROM customers;

Output:

-- COUNT(city) counts only rows where city is NOT NULL
SELECT COUNT(city) AS customers_with_city
FROM customers;

Output:

Seven customers exist, but only 6 have a non-NULL city. Grace's NULL city is excluded from COUNT(city).

COUNT(*) vs COUNT(column): The Complete Comparison

This difference is so important that it deserves a thorough side-by-side comparison:

-- See all three counts in a single query
SELECT COUNT(*) AS total_rows,
       COUNT(city) AS rows_with_city,
       COUNT(email) AS rows_with_email
FROM customers;

Output:

COUNT(DISTINCT column): Count Unique Values

You can combine COUNT with DISTINCT to count the number of unique, non-NULL values in a column:

-- How many unique cities do our customers live in?
SELECT COUNT(DISTINCT city) AS unique_cities
FROM customers;

Output:

There are 4 distinct cities (New York, Los Angeles, Chicago, Seattle). Grace's NULL city is not counted, and duplicate cities (New York appears twice, Chicago appears twice) are collapsed into one.

-- How many unique customers have placed orders?
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Output:

-- How many distinct statuses exist?
SELECT COUNT(DISTINCT status) AS status_count
FROM orders;

Output:

SUM: Adding Values Together

SUM calculates the total of all values in a numeric column. It adds up every non-NULL value and returns the result.

-- Total value of all products in stock
SELECT SUM(stock_quantity) AS total_units_in_stock
FROM products;

Output:

The database added together the stock quantities of all 10 products (150 + 75 + 200 + 50 + 30 + 25 + 100 + 60 + 0 + 180 = 870).

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

Output:

-- Total revenue from completed orders only
SELECT SUM(total_amount) AS completed_revenue
FROM orders
WHERE status = 'completed';

Output:

SUM with Expressions

You can use SUM with calculated expressions, not just raw columns:

-- Total inventory value (price × quantity for each product, then summed)
SELECT SUM(price * stock_quantity) AS total_inventory_value
FROM products;

Output:

The database first calculates price * stock_quantity for each row, then sums all the results.

-- Total revenue from order items (quantity × unit_price for each item)
SELECT SUM(quantity * unit_price) AS calculated_revenue
FROM order_items;

Output:

AVG: Calculating the Average

AVG returns the arithmetic mean of all non-NULL values in a numeric column.

-- Average product price
SELECT AVG(price) AS average_price
FROM products;

Output:

The result may have many decimal places. Use ROUND to clean it up:

SELECT ROUND(AVG(price), 2) AS average_price
FROM products;

Output:

AVG with WHERE

-- Average price of Electronics products (category 1)
SELECT ROUND(AVG(price), 2) AS avg_electronics_price
FROM products
WHERE category_id = 1;

Output:

-- Average order value for completed orders
SELECT ROUND(AVG(total_amount), 2) AS avg_completed_order
FROM orders
WHERE status = 'completed';

Output:

Multiple Averages in One Query

SELECT ROUND(AVG(price), 2) AS avg_price,
       ROUND(AVG(stock_quantity), 2) AS avg_stock,
       ROUND(AVG(price * stock_quantity), 2) AS avg_inventory_value
FROM products;

Output:

MIN: Finding the Smallest Value

MIN returns the smallest value in a column. For numbers, this is the lowest value. For text, it is the value that comes first alphabetically. For dates, it is the earliest date.

MIN with Numbers

-- Cheapest product
SELECT MIN(price) AS lowest_price
FROM products;

Output:

-- Smallest order total
SELECT MIN(total_amount) AS smallest_order
FROM orders;

Output:

MIN with Dates

-- Earliest customer signup
SELECT MIN(signup_date) AS first_signup
FROM customers;

Output:

-- Earliest order date
SELECT MIN(order_date) AS first_order
FROM orders;

Output:

MIN with Text

-- Alphabetically first product name
SELECT MIN(name) AS first_alphabetically
FROM products;

Output:

MAX: Finding the Largest Value

MAX returns the largest value in a column. It is the opposite of MIN.

MAX with Numbers

-- Most expensive product
SELECT MAX(price) AS highest_price
FROM products;

Output:

-- Largest single order
SELECT MAX(total_amount) AS largest_order
FROM orders;

Output:

MAX with Dates

-- Most recent customer signup
SELECT MAX(signup_date) AS latest_signup
FROM customers;

Output:

-- Most recent order date
SELECT MAX(order_date) AS latest_order
FROM orders;

Output:

MIN and MAX Together

A common pattern is using MIN and MAX together to see the range of values:

SELECT MIN(price) AS cheapest,
       MAX(price) AS most_expensive,
       MAX(price) - MIN(price) AS price_range
FROM products;

Output:

SELECT MIN(order_date) AS first_order,
       MAX(order_date) AS latest_order,
       MIN(total_amount) AS smallest_total,
       MAX(total_amount) AS largest_total
FROM orders;

Output:

Combining Multiple Aggregate Functions

One of the most powerful aspects of aggregate functions is that you can use several of them in a single query to build comprehensive summaries:

Product Statistics Dashboard

SELECT COUNT(*) AS total_products,
       COUNT(DISTINCT category_id) AS total_categories,
       ROUND(AVG(price), 2) AS avg_price,
       MIN(price) AS min_price,
       MAX(price) AS max_price,
       SUM(stock_quantity) AS total_stock
FROM products;

Output:

One query, six insights about your product catalog.

Order Analytics

SELECT COUNT(*) AS total_orders,
       COUNT(DISTINCT customer_id) AS unique_customers,
       SUM(total_amount) AS total_revenue,
       ROUND(AVG(total_amount), 2) AS avg_order_value,
       MIN(total_amount) AS smallest_order,
       MAX(total_amount) AS largest_order
FROM orders;

Output:

Filtered Summary

You can combine aggregates with WHERE to summarize subsets of data:

-- Statistics for available products priced over $30
SELECT COUNT(*) AS matching_products,
       ROUND(AVG(price), 2) AS avg_price,
       SUM(stock_quantity) AS total_stock,
       MIN(price) AS cheapest,
       MAX(price) AS most_expensive
FROM products
WHERE is_available = true
  AND price > 30;

Output:

How Aggregate Functions Handle NULL

Understanding how each aggregate function treats NULL values is essential for writing accurate queries. The rules are consistent and straightforward, but ignoring them leads to subtle bugs.

The Core Rule: Aggregates Ignore NULL

All aggregate functions except COUNT(*) skip NULL values entirely. They act as if those rows do not exist.

Let us demonstrate with a concrete example. Consider the city column in the customers table, where Grace has a NULL city:

-- COUNT(*) counts ALL rows (including NULLs)
SELECT COUNT(*) AS all_rows FROM customers;
-- Returns: 7

-- COUNT(city) counts only NON-NULL values
SELECT COUNT(city) AS non_null_cities FROM customers;
-- Returns: 6

NULL Impact on AVG

This is where NULL handling can cause real confusion. AVG divides the sum by the number of non-NULL values, not by the total number of rows.

Consider this example with a hypothetical table:

-- Imagine a ratings table with these values: 5, 3, NULL, 4, NULL

-- AVG ignores NULLs: (5 + 3 + 4) / 3 = 4.0
-- NOT: (5 + 3 + 0 + 4 + 0) / 5 = 2.4

The average is calculated using only the 3 non-NULL values, not all 5 rows. This is usually the correct behavior (a missing rating should not pull the average down), but you need to be aware of it.

NULL Behavior Summary

What Happens When ALL Values Are NULL?

If every value in the column is NULL, aggregates behave as follows:

-- Hypothetical: All cities are NULL
SELECT COUNT(*) AS total_rows,         -- Returns: row count (e.g., 5)
       COUNT(city) AS non_null_count,  -- Returns: 0
       SUM(price) AS total,            -- Returns: NULL
       AVG(price) AS average,          -- Returns: NULL
       MIN(price) AS minimum,          -- Returns: NULL
       MAX(price) AS maximum           -- Returns: NULL
FROM some_table_with_all_nulls;

COUNT returns 0, but SUM, AVG, MIN, and MAX all return NULL when there are no non-NULL values to process.

Aggregates Cannot Mix with Regular Columns

A critical rule: you cannot select a regular column alongside an aggregate function without using GROUP BY. The database does not know which individual row's value to display next to the summarized result.

-- Wrong: Mixing an aggregate with a regular column
SELECT name, AVG(price) AS avg_price
FROM products;
-- ERROR: column "products.name" must appear in the GROUP BY clause
-- or be used in an aggregate function

Why does this fail? AVG(price) produces one number (the average across all products). But name has 10 different values (one per product). The database cannot decide which product name to display next to the single average.

-- Correct: Aggregate only, no regular columns
SELECT AVG(price) AS avg_price
FROM products;

-- Also correct: Use GROUP BY (covered in the next guide)
SELECT category_id, AVG(price) AS avg_price
FROM products
GROUP BY category_id;

Finding Rows That Match Aggregate Results

A common need is finding the specific row that has the minimum or maximum value. A natural instinct is to combine MIN/MAX with regular columns, but as we just learned, that does not work directly.

The Wrong Way

-- Wrong: Cannot mix aggregate with regular column
SELECT name, MAX(price)
FROM products;
-- ERROR (in most databases)

The Correct Way: Subquery

Use the aggregate in a WHERE clause via a subquery:

-- Find the most expensive product
SELECT name, price
FROM products
WHERE price = (SELECT MAX(price) FROM products);

Output:

The subquery SELECT MAX(price) FROM products returns 129.99, and the outer query finds the product with that exact price.

-- Find the cheapest product
SELECT name, price
FROM products
WHERE price = (SELECT MIN(price) FROM products);

Output:

-- Find the most recent order
SELECT id, order_date, total_amount, status
FROM orders
WHERE order_date = (SELECT MAX(order_date) FROM orders);

Output:

Alternative: ORDER BY with LIMIT

For simple "find the top/bottom one" queries, ORDER BY with LIMIT 1 is often simpler:

-- Most expensive product (alternative approach)
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 1;

Output:

Both approaches work. The subquery approach handles ties (multiple products at the same max price), while LIMIT 1 returns only one row even if there are ties.

Practical Aggregate Scenarios

E-Commerce Dashboard Summary

SELECT COUNT(*) AS total_orders,
       COUNT(DISTINCT customer_id) AS unique_buyers,
       SUM(total_amount) AS gross_revenue,
       ROUND(AVG(total_amount), 2) AS avg_order_value,
       MAX(total_amount) AS highest_order,
       MIN(order_date) AS first_order_date,
       MAX(order_date) AS latest_order_date
FROM orders;

Output:

Revenue by Order Status

SELECT status,
       COUNT(*) AS order_count,
       SUM(total_amount) AS revenue,
       ROUND(AVG(total_amount), 2) AS avg_value
FROM orders
GROUP BY status
ORDER BY revenue DESC;

Output:

Inventory Health Check

SELECT COUNT(*) AS total_products,
       SUM(CASE WHEN stock_quantity = 0 THEN 1 ELSE 0 END) AS out_of_stock,
       SUM(CASE WHEN stock_quantity > 0 AND stock_quantity < 30 THEN 1 ELSE 0 END) AS low_stock,
       SUM(CASE WHEN stock_quantity >= 30 THEN 1 ELSE 0 END) AS healthy_stock,
       SUM(stock_quantity) AS total_units,
       ROUND(SUM(price * stock_quantity), 2) AS total_inventory_value
FROM products;

Output:

This single query gives you a complete health snapshot of your inventory, counting products in each stock category and calculating the total value.

Practical Exercises

Exercise 1

Count the total number of orders and the number of unique customers who have placed orders.

SELECT COUNT(*) AS total_orders,
       COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Expected output:

Exercise 2

Find the total revenue, average order value, and the smallest and largest orders for completed orders only.

SELECT SUM(total_amount) AS total_revenue,
       ROUND(AVG(total_amount), 2) AS avg_order_value,
       MIN(total_amount) AS smallest_order,
       MAX(total_amount) AS largest_order
FROM orders
WHERE status = 'completed';

Expected output:

Exercise 3

Find how many products have zero stock and how many have more than 100 units.

SELECT COUNT(CASE WHEN stock_quantity = 0 THEN 1 END) AS out_of_stock,
       COUNT(CASE WHEN stock_quantity > 100 THEN 1 END) AS over_100_units
FROM products;

Expected output:

Exercise 4

Calculate the total inventory value (sum of price times quantity) for available products only.

SELECT ROUND(SUM(price * stock_quantity), 2) AS available_inventory_value
FROM products
WHERE is_available = true;

Expected output:

Exercise 5

Find the name and price of the cheapest available product using a subquery.

SELECT name, price
FROM products
WHERE is_available = true
  AND price = (
      SELECT MIN(price)
      FROM products
      WHERE is_available = true
  );

Expected output:

Key Takeaways

Aggregate functions transform SQL from a row-retrieval tool into a powerful analytical engine. Here is what you should remember:

• Aggregation condenses multiple rows into a single summarized result
• COUNT(*) counts all rows, including those with NULL values
• COUNT(column) counts only rows where the specified column is not NULL
• COUNT(DISTINCT column) counts unique non-NULL values
• SUM(column) adds up all non-NULL numeric values
• AVG(column) calculates the arithmetic mean of non-NULL values (divides by non-NULL count, not total row count)
• MIN(column) returns the smallest non-NULL value (works with numbers, text, and dates)
• MAX(column) returns the largest non-NULL value (works with numbers, text, and dates)
• All aggregates except COUNT(*) ignore NULL values entirely
• When all values are NULL, SUM, AVG, MIN, and MAX return NULL (use COALESCE to provide a default)
• You cannot mix regular columns with aggregate functions unless you use GROUP BY
• Use subqueries or ORDER BY with LIMIT 1 to find the specific row matching a MIN or MAX value
• Multiple aggregate functions can be combined in a single query for comprehensive summaries

Aggregate functions on their own summarize your entire table (or a filtered subset). In the next guide, you will learn GROUP BY, which lets you calculate these summaries per group, answering questions like "what is the average price per category?" and "how many orders does each customer have?"