Aggregate Functions Explained

Master data summarization and analysis using SQL's powerful aggregate functions.

Aggregate functions are essential SQL tools that perform calculations across sets of rows, returning a single value. These functions are crucial for data analysis, reporting, and deriving insights from your data.

The COUNT Function

COUNT is used to calculate the number of rows or values in a result set:

-- Basic COUNT
SELECT COUNT(*) as total_orders 
FROM orders;

-- COUNT with DISTINCT
SELECT COUNT(DISTINCT customer_id) as unique_customers
FROM orders;

Key Points

•COUNT(*) includes all rows
•COUNT(column) excludes NULL values
•COUNT(DISTINCT column) counts unique values

SUM and AVG Functions

SUM Function

Calculate totals and conditional sums:

-- Simple SUM
SELECT SUM(order_total) as total_revenue
FROM orders;

-- SUM with conditions
SELECT 
    category_id,
    SUM(CASE 
        WHEN status = 'completed' THEN order_total 
        ELSE 0 
    END) as completed_revenue
FROM orders
GROUP BY category_id;

AVG Function

Calculate averages and moving averages:

-- Basic average
SELECT AVG(price) as avg_price
FROM products;

-- Moving average
SELECT 
    date,
    AVG(price) OVER (
        ORDER BY date
        ROWS BETWEEN 7 PRECEDING AND CURRENT ROW
    ) as moving_avg_7day
FROM stock_prices;

MIN and MAX Functions

Find extreme values within groups:

SELECT 
    category_name,
    MIN(price) as lowest_price,
    MAX(price) as highest_price,
    AVG(price) as avg_price
FROM products
GROUP BY category_name;

Using GROUP BY

Group data and apply aggregate functions to each group:

SELECT 
    department,
    COUNT(*) as employee_count,
    AVG(salary) as avg_salary,
    MAX(salary) as highest_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;

Best Practices

•Include all non-aggregated columns in GROUP BY
•Use HAVING for filtering aggregated results
•Consider indexing GROUP BY columns

Common Mistakes

•Missing columns in GROUP BY clause
•Using WHERE instead of HAVING
•Incorrect grouping levels

Window Functions

Use window functions to perform calculations across related rows:

SELECT 
    product_name,
    category_name,
    price,
    AVG(price) OVER (
        PARTITION BY category_name
    ) as category_avg_price,
    price - AVG(price) OVER (
        PARTITION BY category_name
    ) as diff_from_avg
FROM products
JOIN categories ON products.category_id = categories.id;

Applications

•Running totals and averages
•Ranking and row numbering
•Comparative analytics

Practice Examples

Try these examples to practice using aggregate functions:

Sales Analysis

SELECT category_name, COUNT(*) as total_sales, SUM(amount) as revenue FROM sales GROUP BY category_name HAVING SUM(amount) > 10000;

Customer Metrics

SELECT customer_id, COUNT(*) as order_count, AVG(order_total) as avg_order_value FROM orders GROUP BY customer_id;

Product Statistics

SELECT category_id, MIN(price) as min_price, MAX(price) as max_price, AVG(price) as avg_price FROM products GROUP BY category_id;

Next Steps

Take your data analysis skills further with these related topics:

Window Functions Deep Dive →

Master advanced analytical functions

Working with JOINs →

Combine aggregates across multiple tables