Aggregate functions perform calculations on sets of rows and return a single result. They’re essential for data analysis in SQL.

Common Aggregate Functions

COUNT

Count rows or non-null values:

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

-- Count non-null values
SELECT COUNT(email) as emails_provided FROM customers;

-- Count distinct values
SELECT COUNT(DISTINCT category) as unique_categories FROM products;

SUM

Add up numeric values:

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

-- Sum with condition
SELECT SUM(amount) as high_value_total 
FROM orders 
WHERE amount > 100;

AVG

Calculate average:

-- Average order value
SELECT AVG(amount) as avg_order FROM orders;

-- Average with rounding
SELECT ROUND(AVG(amount), 2) as avg_order FROM orders;

MIN and MAX

Find extreme values:

-- Price range
SELECT 
    MIN(price) as lowest_price,
    MAX(price) as highest_price
FROM products;

-- Date range
SELECT 
    MIN(order_date) as first_order,
    MAX(order_date) as latest_order
FROM orders;

Combining Aggregate Functions

SELECT 
    COUNT(*) as total_orders,
    SUM(amount) as total_revenue,
    AVG(amount) as avg_order,
    MIN(amount) as smallest_order,
    MAX(amount) as largest_order
FROM orders;

Using with GROUP BY

-- Aggregates per category
SELECT 
    category,
    COUNT(*) as product_count,
    AVG(price) as avg_price,
    SUM(stock) as total_stock
FROM products
GROUP BY category;

-- Multiple grouping columns
SELECT 
    category,
    brand,
    COUNT(*) as product_count,
    AVG(price) as avg_price
FROM products
GROUP BY category, brand;

Filtering with HAVING

-- Categories with more than 10 products
SELECT 
    category,
    COUNT(*) as product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 10;

-- High revenue categories
SELECT 
    category,
    SUM(price * quantity) as revenue
FROM order_items
JOIN products ON order_items.product_id = products.id
GROUP BY category
HAVING SUM(price * quantity) > 10000;

Window Functions (Advanced)

Running Total

SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;

Row Number and Ranking

-- Rank products by price within category
SELECT 
    category,
    name,
    price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank
FROM products;

-- Top 3 products per category
SELECT * FROM (
    SELECT 
        category,
        name,
        price,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank
    FROM products
) ranked
WHERE rank <= 3;

Moving Average

SELECT 
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7day
FROM orders;

Conditional Aggregation

CASE with Aggregate

SELECT 
    COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed,
    COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) as cancelled
FROM orders;

FILTER Clause (PostgreSQL)

SELECT 
    COUNT(*) FILTER (WHERE status = 'completed') as completed,
    COUNT(*) FILTER (WHERE status = 'pending') as pending,
    SUM(amount) FILTER (WHERE status = 'completed') as completed_revenue
FROM orders;

Practical Examples

Sales Report

SELECT 
    EXTRACT(YEAR FROM order_date) as year,
    EXTRACT(MONTH FROM order_date) as month,
    COUNT(*) as order_count,
    SUM(amount) as revenue,
    AVG(amount) as avg_order_value,
    COUNT(DISTINCT customer_id) as unique_customers
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY 
    EXTRACT(YEAR FROM order_date),
    EXTRACT(MONTH FROM order_date)
ORDER BY year, month;

Customer Segmentation

SELECT 
    CASE 
        WHEN total_spent >= 1000 THEN 'VIP'
        WHEN total_spent >= 500 THEN 'Regular'
        ELSE 'Basic'
    END as segment,
    COUNT(*) as customer_count,
    AVG(total_spent) as avg_spent
FROM (
    SELECT 
        customer_id,
        SUM(amount) as total_spent
    FROM orders
    GROUP BY customer_id
) customer_totals
GROUP BY segment;

Inventory Analysis

SELECT 
    category,
    COUNT(*) as total_products,
    SUM(CASE WHEN stock = 0 THEN 1 ELSE 0 END) as out_of_stock,
    SUM(CASE WHEN stock < 10 THEN 1 ELSE 0 END) as low_stock,
    AVG(stock) as avg_stock_level,
    SUM(stock * price) as inventory_value
FROM products
GROUP BY category
ORDER BY inventory_value DESC;

NULL Handling

-- COUNT(*) includes NULLs, COUNT(column) excludes them
SELECT 
    COUNT(*) as total_rows,
    COUNT(email) as with_email,
    COUNT(*) - COUNT(email) as without_email
FROM customers;

-- COALESCE for default values
SELECT AVG(COALESCE(rating, 0)) as avg_rating FROM reviews;

-- NULL-safe aggregation
SELECT SUM(COALESCE(discount, 0)) as total_discounts FROM orders;

Summary

COUNT - count rows or values
SUM - total of values
AVG - average of values
MIN/MAX - extreme values
Use GROUP BY for aggregates per group
Use HAVING to filter grouped results
Use CASE for conditional aggregation
Window functions for running totals and rankings

Settings

Appearance