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

Aggregate functions collapse many rows into a single computed value. They're the foundation of any analytical query.

Function	What it returns
COUNT	Number of rows (or non-NULL values)
SUM	Total of all values
AVG	Average of all values
MIN	Smallest value
MAX	Largest value

Prior lessons

This lesson pairs closely with GROUP BY and HAVING. The sample dataset is defined in the Introduction to SQL lesson.

Syntax

SELECT
    COUNT(*) AS total_rows,
    SUM(column)  AS total,
    AVG(column)  AS average,
    MIN(column)  AS minimum,
    MAX(column)  AS maximum
FROM table_name;

Examples

Example 1: Overall order statistics

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

Result:

total_orders	orders_with_customer	total_revenue	avg_order_value	smallest_order	largest_order
10	10	4715.78	471.58	24.95	999.00

Example 2: Revenue and order count per customer

SELECT
    customer_id,
    COUNT(*)          AS order_count,
    SUM(total_amount) AS total_spent,
    AVG(total_amount) AS avg_order_value,
    MAX(total_amount) AS largest_order
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

Result:

customer_id	order_count	total_spent	avg_order_value	largest_order
3	2	1795.00	897.50	999.00
1	3	1407.98	469.33	999.00
2	2	1348.00	674.00	999.00
5	1	89.97	89.97	89.97
4	2	74.85	37.43	49.90

Example 3: MIN and MAX on product prices by category

SELECT
    category,
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive,
    AVG(price) AS avg_price
FROM products
GROUP BY category;

Result:

category	cheapest	most_expensive	avg_price
Electronics	29.99	999.00	514.50
Furniture	199.00	349.00	274.00
Stationery	4.99	4.99	4.99

COUNT(*) vs COUNT(column)

SELECT
    COUNT(*)           AS all_rows,        -- counts every row, including NULLs
    COUNT(customer_id) AS non_null_count   -- skips rows where customer_id is NULL
FROM orders;

If all customer_id values are non-NULL, both return the same number. If some are NULL, COUNT(customer_id) is smaller.

Common Mistake

AVG ignores NULLs - which can mislead

AVG automatically skips NULL values. If 40% of a column is NULL, AVG computes the average over only the 60% that have values. This may not be what you want.

Use COUNT(*) vs COUNT(column) to check before trusting an average:

SELECT
    COUNT(*)           AS total_rows,
    COUNT(total_amount) AS non_null_rows
FROM orders;

If the two numbers differ, AVG(total_amount) is computing over fewer rows than the table actually has.

Practice

Write a query that returns the total number of orders, total revenue, smallest order amount, and largest order amount across all orders.

Show answer

SELECT
    COUNT(*)          AS total_orders,
    SUM(total_amount) AS total_revenue,
    MIN(total_amount) AS smallest_order,
    MAX(total_amount) AS largest_order
FROM orders;

Expected result:

total_orders	total_revenue	smallest_order	largest_order
10	4715.78	24.95	999.00