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

Aggregate functions in SQL take multiple rows and return a single value. They are the foundation of reporting, analytics, and data summarization. Every time you need a total, an average, a count, or an extreme value from a dataset, you use an aggregate function.

The Five Core Aggregate Functions

COUNT

Returns the number of rows or non-NULL values.

-- Count all rows
SELECT COUNT(*) FROM orders;

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

-- Count distinct values
SELECT COUNT(DISTINCT country) FROM customers;

Important distinction: COUNT(*) counts all rows including those with NULLs. COUNT(column_name) only counts rows where that column is not NULL.

SUM

Returns the total of all values in a column.

-- Total revenue
SELECT SUM(amount) FROM orders;

-- Total by category
SELECT category, SUM(amount) AS total_revenue
FROM orders
GROUP BY category;

SUM ignores NULL values. If all values are NULL, it returns NULL (not zero).

AVG

Returns the arithmetic mean of all values.

-- Average order value
SELECT AVG(amount) AS avg_order_value FROM orders;

-- Average excluding outliers
SELECT AVG(amount) AS avg_order_value
FROM orders
WHERE amount BETWEEN 10 AND 10000;

AVG also ignores NULLs. This matters: if you have 10 rows but 3 have NULL in the amount column, AVG divides by 7, not 10.

MIN

Returns the smallest value.

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

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

-- Shortest name
SELECT MIN(LENGTH(name)) AS shortest_name_length FROM customers;

MIN works on numbers, dates, and strings (alphabetical order for strings).

MAX

Returns the largest value.

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

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

-- Highest revenue customer
SELECT customer_id, MAX(total_spent) FROM customer_summary;

GROUP BY: Aggregating by Category

Aggregate functions become powerful when combined with GROUP BY, which splits rows into groups before applying the aggregation.

SELECT
    department,
    COUNT(*) AS headcount,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    SUM(salary) AS total_payroll
FROM employees
GROUP BY department
ORDER BY total_payroll DESC;

Result:

Multi-Column GROUP BY

Group by multiple dimensions:

SELECT
    EXTRACT(YEAR FROM order_date) AS year,
    EXTRACT(MONTH FROM order_date) AS month,
    product_category,
    COUNT(*) AS order_count,
    SUM(amount) AS revenue
FROM orders
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3;

HAVING: Filtering Aggregated Results

WHERE filters rows before aggregation. HAVING filters groups after aggregation.

-- Departments with more than 10 employees
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

-- Products with average rating below 3
SELECT product_id, AVG(rating) AS avg_rating, COUNT(*) AS review_count
FROM reviews
GROUP BY product_id
HAVING AVG(rating) < 3.0 AND COUNT(*) >= 5;

Common mistake: Using WHERE instead of HAVING for aggregate conditions:

-- WRONG: WHERE cannot reference aggregates
SELECT department, AVG(salary)
FROM employees
WHERE AVG(salary) > 100000  -- Error!
GROUP BY department;

-- CORRECT: Use HAVING
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 100000;

Combining Aggregates with CASE

Conditional aggregation is one of the most useful patterns in SQL:

SELECT
    product_category,
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
    ROUND(
        SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*),
        1
    ) AS completion_rate
FROM orders
GROUP BY product_category;

NULL Handling in Aggregates

All aggregate functions (except COUNT(*)) ignore NULL values. This has implications:

-- Sample data: amounts = [100, 200, NULL, 400]

SELECT
    COUNT(*) AS total_rows,          -- 4
    COUNT(amount) AS non_null_count, -- 3
    SUM(amount) AS total,            -- 700
    AVG(amount) AS average,          -- 233.33 (700/3, not 700/4)
    MIN(amount) AS minimum,          -- 100
    MAX(amount) AS maximum           -- 400
FROM orders;

To treat NULL as zero:

SELECT AVG(COALESCE(amount, 0)) AS avg_with_nulls_as_zero FROM orders;
-- Returns 175 (700/4)

Advanced Aggregate Patterns

Running Totals (Window Aggregates)

SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total,
    AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7day_avg
FROM daily_revenue;

Percentage of Total

SELECT
    product_category,
    SUM(revenue) AS category_revenue,
    ROUND(SUM(revenue) * 100.0 / SUM(SUM(revenue)) OVER(), 1) AS pct_of_total
FROM sales
GROUP BY product_category
ORDER BY category_revenue DESC;

Top-N Per Group

WITH ranked AS (
    SELECT
        department,
        employee_name,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
    FROM employees
)
SELECT department, employee_name, salary
FROM ranked
WHERE rank <= 3;

Aggregating with DISTINCT

SELECT
    COUNT(DISTINCT customer_id) AS unique_customers,
    COUNT(*) AS total_orders,
    ROUND(COUNT(*) * 1.0 / COUNT(DISTINCT customer_id), 1) AS orders_per_customer,
    SUM(amount) / COUNT(DISTINCT customer_id) AS revenue_per_customer
FROM orders
WHERE order_date >= '2026-01-01';

Database-Specific Aggregate Functions

Beyond the standard five, databases offer additional aggregates:

STRING_AGG Example

-- Concatenate all tags for each product
SELECT
    product_id,
    STRING_AGG(tag_name, ', ' ORDER BY tag_name) AS all_tags
FROM product_tags
GROUP BY product_id;

Performance Tips

1. Index your GROUP BY columns. Grouping requires sorting; indexes make it faster.
2. Filter before aggregating. Use WHERE to reduce rows before GROUP BY processes them.
3. Avoid SELECT * with aggregates. Only select the columns you need.
4. Use approximate functions for large datasets. Many databases offer APPROX_COUNT_DISTINCT for billion-row tables.
5. Pre-aggregate in materialized views. If you run the same aggregation repeatedly, store the result.

Real-World Query Examples

Monthly revenue with growth rate:

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0
        / LAG(revenue) OVER (ORDER BY month), 1) AS growth_pct
FROM monthly
ORDER BY month;

Customer segmentation by purchase frequency:

SELECT
    CASE
        WHEN order_count >= 10 THEN 'VIP'
        WHEN order_count >= 5 THEN 'Regular'
        WHEN order_count >= 2 THEN 'Occasional'
        ELSE 'One-time'
    END AS segment,
    COUNT(*) AS customer_count,
    AVG(total_spent) AS avg_revenue
FROM (
    SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
) customer_stats
GROUP BY 1
ORDER BY avg_revenue DESC;

Platforms like Skopx let teams skip writing these queries manually. Ask "What is our monthly revenue growth rate?" or "How many VIP customers do we have?" in plain English and get the aggregated answer instantly.

Summary

Master these functions with GROUP BY, HAVING, and conditional CASE expressions, and you can answer most business questions directly in SQL.