Aggregate functions perform calculations on multiple rows and return a single result. They're essential for data analysis, reporting, and understanding your data at a glance.

The Five Core Aggregate Functions

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 excluding NULLs (automatic)
SELECT AVG(rating) as avg_rating
FROM product_reviews
WHERE rating IS NOT NULL;

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;

Using Aggregates with GROUP BY

Combine aggregate functions with GROUP BY to calculate summaries for each group:

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;

Multiple Aggregates in One Query

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

Use CASE statements inside aggregates for powerful analysis:

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;

Practical Business Reports

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;