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

Learn how to summarize data across rows using SQL's five core aggregate functions so you can answer business questions like totals, averages, and counts.

What Are Aggregate Functions?

Every day, an ecommerce business needs answers like "How many orders came in today?" or "What is the average order value this month?" Aggregate functions collapse many rows into a single summary value. They are the foundation of any reporting query you will write.

COUNT

COUNT tells you how many rows match your criteria.

-- How many customers do we have?
SELECT COUNT(*) AS total_customers
FROM customers c;

-- How many customers have a phone number on file?
SELECT COUNT(c.phone) AS customers_with_phone
FROM customers c;

COUNT(*) counts every row. COUNT(column) skips NULL values in that column.

Key idea: Use COUNT(*) when you want total rows. Use COUNT(column) when you want to know how many rows have a value in a specific column.

SUM

SUM adds up numeric values. Use it for totals like revenue, quantities, or discounts.

-- Total revenue from all completed orders
SELECT SUM(o.total_amount) AS total_revenue
FROM orders o
WHERE o.status = 'delivered';

-- Total quantity sold per product
SELECT oi.product_id,
       SUM(oi.quantity) AS total_units_sold
FROM order_items oi
GROUP BY oi.product_id;

AVG

AVG returns the mean of a numeric column, ignoring NULL values.

-- Average order value
SELECT AVG(o.total_amount) AS avg_order_value
FROM orders o;

-- Average unit price per category
SELECT p.category_id,
       AVG(p.price) AS avg_price
FROM products p
GROUP BY p.category_id;

MIN and MAX

MIN and MAX return the smallest and largest values respectively. They work on numbers, dates, and text.

-- Earliest and most recent order dates
SELECT MIN(o.order_date) AS first_order,
       MAX(o.order_date) AS latest_order
FROM orders o;

-- Cheapest and most expensive product per category
SELECT p.category_id,
       MIN(p.price) AS cheapest,
       MAX(p.price) AS most_expensive
FROM products p
GROUP BY p.category_id;

Combining Aggregates

You can use multiple aggregate functions in the same SELECT. This is how most summary reports are built.

-- Order summary stats
SELECT COUNT(*)             AS total_orders,
       SUM(o.total_amount)  AS revenue,
       AVG(o.total_amount)  AS avg_order_value,
       MIN(o.total_amount)  AS smallest_order,
       MAX(o.total_amount)  AS largest_order
FROM orders o
WHERE o.status = 'delivered';

Common Mistakes

-- wrong
SELECT COUNT(*)
FROM orders o
WHERE o.status = 'delivered'
HAVING COUNT(*) > 100;

HAVING filters grouped results. Without GROUP BY, this works on the single aggregate row, which is usually not what you intend.

-- correct
SELECT o.status,
       COUNT(*) AS total
FROM orders o
GROUP BY o.status
HAVING COUNT(*) > 100;

-- wrong
SELECT AVG(oi.discount_pct) AS avg_discount
FROM order_items oi
WHERE oi.discount_pct != NULL;

You cannot compare against NULL with !=. Use IS NOT NULL instead.

-- correct
SELECT AVG(oi.discount_pct) AS avg_discount
FROM order_items oi
WHERE oi.discount_pct IS NOT NULL;

-- wrong
SELECT p.category_id, p.product_name, COUNT(*)
FROM products p
GROUP BY p.category_id;

Every non-aggregate column in SELECT must appear in GROUP BY. p.product_name is missing here.

-- correct
SELECT p.category_id, COUNT(*) AS product_count
FROM products p
GROUP BY p.category_id;

Quick Recap

COUNT(*) counts all rows; COUNT(column) skips NULLs in that column
SUM, AVG, MIN, and MAX all ignore NULL values automatically
You can mix multiple aggregate functions in a single query
Every non-aggregate column in SELECT must also appear in GROUP BY
Use HAVING to filter on aggregate results, not WHERE

Exercise 1 Easy

Find the total number of products in the products table, and separately count how many products are currently active (is_active = 1).

Hint: Use COUNT(*) for total rows and COUNT with a CASE or a WHERE filter for active products. You can do both in one query using conditional aggregation.

Solution

SELECT COUNT(*) AS total_products, SUM(CASE WHEN p.is_active = 1 THEN 1 ELSE 0 END) AS active_products FROM products p;

Exercise 2 Medium

For each payment method, find the total amount collected and the average payment amount. Only include rows where the payment status is 'completed'.

Hint: Filter with WHERE first, then GROUP BY the payment method column and apply SUM and AVG.

Solution

SELECT pay.method, SUM(pay.amount) AS total_collected, AVG(pay.amount) AS avg_payment FROM payments pay WHERE pay.status = 'completed' GROUP BY pay.method;

Exercise 3 Hard

List each product category name along with the total revenue generated from sold order items (use line_total from order_items). Only include categories where total revenue exceeds 5000. Sort by total revenue descending.

Hint: You need to JOIN order_items to products to categories. GROUP BY the category name, then filter with HAVING on the SUM.

Solution

SELECT cat.category_name, SUM(oi.line_total) AS total_revenue FROM order_items oi JOIN products p ON oi.product_id = p.product_id JOIN categories cat ON p.category_id = cat.category_id GROUP BY cat.category_name HAVING SUM(oi.line_total) > 5000 ORDER BY total_revenue DESC;

Frequently Asked Questions

What are aggregate functions in SQL?

Aggregate functions compute a single result from a set of rows. Examples include COUNT, SUM, AVG, MIN, and MAX.

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts all rows including NULLs. COUNT(column) counts only rows where that column is not NULL.

Can I use aggregate functions without GROUP BY?

Yes. Without GROUP BY, the aggregate runs across the entire result set and returns one row.

Why does AVG ignore NULL values?

SQL aggregate functions skip NULLs by default, so AVG divides only by the count of non-NULL values.