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

SQL aggregate functions take a set of rows and return a single value: COUNT counts rows, SUM totals a column, AVG averages it, and MIN and MAX return its smallest and largest value. Without GROUP BY they summarize the whole table; with it, they return one result per group.

What Does Each SQL Aggregate Function Do?

COUNT counts rows (or the non-NULL values in one column), SUM and AVG total and average a numeric column, and MIN and MAX return the smallest and largest value of numbers, dates or text. To count unique values instead of rows, use COUNT(DISTINCT column), covered in depth in SQL COUNT DISTINCT.

COUNT() - Count Rows

-- Count all rows
SELECT COUNT(*) as total_customers
FROM customers;

-- Count non-NULL values in a specific column
SELECT COUNT(email) as customers_with_email
FROM customers;

-- Count unique values
SELECT COUNT(DISTINCT city) as unique_cities
FROM customers;

SUM() - Add Up Values

-- Total revenue
SELECT SUM(total) as total_revenue
FROM orders;

-- Sum with condition
SELECT SUM(CASE WHEN status = 'completed' THEN total ELSE 0 END) as completed_revenue
FROM orders;

AVG() - Calculate Average

-- Average order value
SELECT AVG(total) as average_order_value
FROM orders;

-- Average rating (NULL ratings are skipped automatically)
SELECT AVG(rating) as avg_rating
FROM product_reviews;

MIN() and MAX() - Find Extremes

-- Smallest and largest orders
SELECT
    MIN(total) as smallest_order,
    MAX(total) as largest_order
FROM orders;

-- Date ranges
SELECT
    MIN(order_date) as first_order,
    MAX(order_date) as most_recent_order
FROM orders;

How Do SQL Aggregate Functions Handle NULLs?

They skip them: SUM, AVG, MIN, MAX and COUNT(column) ignore NULL values, while COUNT(*) counts every row. That is why COUNT(*) and COUNT(email) can return different numbers, and why AVG divides by the number of non-NULL values rather than the number of rows.

-- product_reviews.rating holds 5, 4, NULL, 3
SELECT
    COUNT(*)                 AS all_rows,     -- 4
    COUNT(rating)            AS rated_rows,   -- 3
    SUM(rating)              AS rating_sum,   -- 12
    AVG(rating)              AS avg_rating,   -- 4  (12 / 3)
    AVG(COALESCE(rating, 0)) AS avg_as_zero   -- 3  (12 / 4)
FROM product_reviews;

Two more cases catch people out. Over zero rows, COUNT returns 0 but SUM, AVG, MIN and MAX return NULL, so write COALESCE(SUM(total), 0) when a report needs a zero (more in COALESCE and NULLIF). And in SQL Server, AVG of an INT column returns an INT, so the average of 1 and 2 is 1; cast first with AVG(CAST(rating AS DECIMAL(10,2))).

How Do You Use Aggregate Functions with GROUP BY?

List the grouping column in both SELECT and GROUP BY, and each aggregate is calculated separately per group, returning one row per group. Every non-aggregated column in SELECT must normally appear in GROUP BY too; PostgreSQL, SQL Server and MySQL's default ONLY_FULL_GROUP_BY mode reject queries that break this rule.

Sales by Category

SELECT
    category,
    COUNT(*) as product_count,
    SUM(price * stock_quantity) as inventory_value,
    AVG(price) as avg_price,
    MIN(price) as cheapest_product,
    MAX(price) as most_expensive
FROM products
GROUP BY category
ORDER BY inventory_value DESC;

Customer Purchase Behavior

SELECT
    customer_id,
    COUNT(order_id) as total_orders,
    SUM(total) as lifetime_value,
    AVG(total) as avg_order_value,
    MIN(order_date) as first_purchase,
    MAX(order_date) as last_purchase
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) >= 5
ORDER BY lifetime_value DESC;

HAVING filters groups after the aggregates are calculated, which is why COUNT(order_id) >= 5 goes there: WHERE filters individual rows before grouping and can't contain an aggregate. ORDER BY runs last, so it can sort by an aggregate's alias such as lifetime_value. See HAVING vs WHERE for the full order of evaluation.

Can You Use Multiple Aggregate Functions in One Query?

Yes. List as many aggregates as you need in SELECT; each one is calculated over the same filtered rows and the same groups, so one query can return order count, unique customers, revenue and average order value per month. DATE_TRUNC('month', order_date) is PostgreSQL syntax; in MySQL, use DATE_FORMAT(order_date, '%Y-%m-01') in both SELECT and GROUP BY.

SELECT
    DATE_TRUNC('month', order_date) as month,
    COUNT(*) as order_count,
    COUNT(DISTINCT customer_id) as unique_customers,
    SUM(total) as revenue,
    AVG(total) as avg_order_value,
    MIN(total) as min_order,
    MAX(total) as max_order
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Conditional Aggregation

Put a CASE expression inside an aggregate to count or total only the rows that match a condition, so one query returns several filtered totals side by side. COUNT(CASE WHEN rating >= 4 THEN 1 END) works because a CASE with no ELSE returns NULL for non-matching rows, and COUNT skips NULLs.

SELECT
    product_id,
    product_name,
    COUNT(*) as total_reviews,
    COUNT(CASE WHEN rating >= 4 THEN 1 END) as positive_reviews,
    COUNT(CASE WHEN rating <= 2 THEN 1 END) as negative_reviews,
    AVG(rating) as avg_rating,
    SUM(CASE WHEN verified_purchase THEN 1 ELSE 0 END) as verified_count
FROM product_reviews
GROUP BY product_id, product_name
HAVING COUNT(*) >= 10
ORDER BY avg_rating DESC;

PostgreSQL 9.4+ and SQLite 3.30+ also accept the shorter COUNT(*) FILTER (WHERE rating >= 4). More patterns are in CASE WHEN statements.

Practical Business Reports

Real reports combine aggregates with joins and date filters, which adds one trap: after a join, each parent row repeats once per matching child, so count customers or products with COUNT(DISTINCT ...) rather than counting joined rows (see why joins inflate SUM and COUNT). The daily summary uses MySQL date functions.

Daily Sales Summary

SELECT
    DATE(order_date) as sale_date,
    COUNT(*) as orders,
    COUNT(DISTINCT customer_id) as customers,
    SUM(total) as revenue,
    AVG(total) as avg_order_value,
    SUM(total) / COUNT(DISTINCT customer_id) as revenue_per_customer
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY DATE(order_date)
ORDER BY sale_date DESC;

Product Performance Dashboard

SELECT
    p.category,
    COUNT(DISTINCT p.product_id) as products_in_category,
    SUM(oi.quantity) as units_sold,
    SUM(oi.quantity * oi.price) as revenue,
    AVG(oi.price) as avg_selling_price,
    COUNT(DISTINCT o.customer_id) as unique_buyers
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= '2024-01-01'
GROUP BY p.category
ORDER BY revenue DESC;