Skip to main content

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:

average_price
61.045

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

When Do You Need Aggregation?

Whenever your question starts with "how many," "what is the total," "what is the average," "what is the highest," or "what is the lowest," you need an aggregate function.

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:

total_products
10

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

SELECT COUNT(*) AS total_orders
FROM orders;

Output:

total_orders
8
SELECT COUNT(*) AS total_customers
FROM customers;

Output:

total_customers
7

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:

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

Output:

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

Output:

new_customers_2024
3

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:

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

Output:

customers_with_city
6

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:

ExpressionWhat It CountsIncludes NULL?Result (customers table)
COUNT(*)All rowsYes7
COUNT(city)Rows where city is not NULLNo6
COUNT(email)Rows where email is not NULLNo7 (all have emails)
-- 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:

total_rowsrows_with_cityrows_with_email
767
Common Mistake: Assuming COUNT(column) and COUNT(*) Are the Same
-- These are NOT the same when the column contains NULL values
SELECT COUNT(*) FROM customers;          -- Returns 7
SELECT COUNT(city) FROM customers;       -- Returns 6

-- If a column has no NULLs, they happen to return the same number
SELECT COUNT(*) FROM customers;          -- Returns 7
SELECT COUNT(email) FROM customers;      -- Returns 7 (no NULL emails)
-- Same result here, but for different reasons

Use COUNT(*) when you want the total number of rows. Use COUNT(column) when you specifically want to know how many rows have a value in that column.

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:

unique_cities
4

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:

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

Output:

status_count
3

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:

total_units_in_stock
870

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
870.42
-- Total revenue from completed orders only
SELECT SUM(total_amount) AS completed_revenue
FROM orders
WHERE status = 'completed';

Output:

completed_revenue
287.46

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:

total_inventory_value
41420.20

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:

calculated_revenue
872.41
SUM Only Works with Numbers
-- Wrong: SUM cannot add text values
SELECT SUM(name) FROM products;
-- ERROR: function sum(character varying) does not exist

-- Correct: SUM only works with numeric columns
SELECT SUM(price) FROM products;

If you try to SUM a non-numeric column, the database will return an error.

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:

average_price
61.045

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

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

Output:

average_price
61.05

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:

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

Output:

avg_completed_order
95.82

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:

avg_priceavg_stockavg_inventory_value
61.0587.004142.02

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:

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

Output:

smallest_order
34.99

MIN with Dates​

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

Output:

first_signup
2023-01-15
-- Earliest order date
SELECT MIN(order_date) AS first_order
FROM orders;

Output:

first_order
2024-01-10

MIN with Text​

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

Output:

first_alphabetically
Bluetooth Speaker

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:

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

Output:

largest_order
199.98

MAX with Dates​

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

Output:

latest_signup
2024-04-01
-- Most recent order date
SELECT MAX(order_date) AS latest_order
FROM orders;

Output:

latest_order
2024-04-10

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:

cheapestmost_expensiveprice_range
24.99129.99105.00
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:

first_orderlatest_ordersmallest_totallargest_total
2024-01-102024-04-1034.99199.98

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:

total_productstotal_categoriesavg_pricemin_pricemax_pricetotal_stock
10461.0524.99129.99870

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:

total_ordersunique_customerstotal_revenueavg_order_valuesmallest_orderlargest_order
86870.42108.8034.99199.98

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:

matching_productsavg_pricetotal_stockcheapestmost_expensive
770.0754034.99129.99

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​

FunctionNULL HandlingExample
COUNT(*)Counts all rows, including NULLs7 rows = 7
COUNT(column)Counts only non-NULL values6 non-NULL cities = 6
SUM(column)Adds only non-NULL valuesSkips NULL rows
AVG(column)Averages only non-NULL valuesDivides by non-NULL count
MIN(column)Finds minimum among non-NULL valuesIgnores NULLs
MAX(column)Finds maximum among non-NULL valuesIgnores NULLs

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.

Handling NULL Results from Aggregates

Use COALESCE to replace a NULL aggregate result with a default value:

SELECT COALESCE(SUM(total_amount), 0) AS revenue
FROM orders
WHERE status = 'cancelled';
-- No cancelled orders exist, so SUM returns NULL
-- COALESCE converts it to 0

Output:

revenue
0

Without COALESCE, the result would be NULL, which can cause problems in application code that expects a number.

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;
This Is the Most Common Aggregate Error

Almost every beginner encounters this error at some point. The rule is simple:

Every column in your SELECT list must either be:

  1. Inside an aggregate function (COUNT(), SUM(), AVG(), MIN(), MAX()), or
  2. Listed in a GROUP BY clause

If a column is neither aggregated nor grouped, the database raises an error (except MySQL, which may silently return unpredictable results in some configurations).

-- Error: 'name' is not aggregated and not in GROUP BY
SELECT name, COUNT(*) FROM products;

-- Fix option 1: Remove the non-aggregated column
SELECT COUNT(*) FROM products;

-- Fix option 2: Aggregate the column too
SELECT MIN(name), COUNT(*) FROM products;

-- Fix option 3: Add GROUP BY (next guide)
SELECT category_id, COUNT(*) 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:

nameprice
Coffee Maker Pro129.99

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:

nameprice
Stainless Water Bottle24.99
-- Find the most recent order
SELECT id, order_date, total_amount, status
FROM orders
WHERE order_date = (SELECT MAX(order_date) FROM orders);

Output:

idorder_datetotal_amountstatus
82024-04-10199.98pending

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:

nameprice
Coffee Maker Pro129.99

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:

total_ordersunique_buyersgross_revenueavg_order_valuehighest_orderfirst_order_datelatest_order_date
86870.42108.80199.982024-01-102024-04-10

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:

statusorder_countrevenueavg_value
pending3297.9699999999999799.32
completed3287.4695.82
shipped2284.99142.50
Preview: GROUP BY

This example uses GROUP BY, which you will learn in the next guide. For now, notice how aggregates combined with GROUP BY let you calculate summaries per group rather than for the entire table. This is where aggregate functions become truly powerful.

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:

total_productsout_of_stocklow_stockhealthy_stocktotal_unitstotal_inventory_value
10118870414200.20

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:

total_ordersunique_customers
86

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:

total_revenueavg_order_valuesmallest_orderlargest_order
287.4695.8277.49119.98

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:

out_of_stockover_100_units
13

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:

available_inventory_value
41420.20

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:

nameprice
Stainless Water Bottle24.99

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?"

Table of Contents