Aggregation and Grouping

So far, you've been retrieving individual rows from your database. But what if you want to answer questions like "How many orders were placed?" or "What's the average price of products in each category?" That's where aggregate functions come in. They let you summarize large sets of data into meaningful numbers.

Basic Aggregate Functions

SQL provides five core aggregate functions that work across rows to produce a single result.

COUNT, SUM, and AVG

-- Create a sales table
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    product TEXT NOT NULL,
    category TEXT NOT NULL,
    quantity INTEGER,
    price REAL,
    sale_date TEXT
);

-- Insert sales data
INSERT INTO sales (id, product, category, quantity, price, sale_date)
VALUES
    (1, 'Laptop', 'Electronics', 2, 999.99, '2025-01-10'),
    (2, 'Mouse', 'Electronics', 10, 24.99, '2025-01-11'),
    (3, 'Desk', 'Furniture', 3, 299.99, '2025-01-12'),
    (4, 'Chair', 'Furniture', 5, 199.99, '2025-01-13'),
    (5, 'Keyboard', 'Electronics', 8, 79.99, '2025-01-14'),
    (6, 'Monitor', 'Electronics', 4, 349.99, '2025-01-15'),
    (7, 'Lamp', 'Furniture', 12, 49.99, '2025-01-16'),
    (8, 'Headphones', 'Electronics', 6, 149.99, '2025-01-17');

-- Count total number of sales
SELECT COUNT(*) AS total_sales FROM sales;

-- Sum of all quantities sold
SELECT SUM(quantity) AS total_items_sold FROM sales;

-- Average price across all products
SELECT AVG(price) AS average_price FROM sales;

-- All three at once
SELECT
    COUNT(*) AS total_sales,
    SUM(quantity) AS total_items_sold,
    ROUND(AVG(price), 2) AS average_price
FROM sales;

COUNT(*) counts all rows, including those with NULL values.
COUNT(column) counts only non-NULL values in that column.
SUM(column) adds up all numeric values.
AVG(column) calculates the arithmetic mean.

MIN and MAX

-- Create an employees table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT NOT NULL,
    salary INTEGER,
    hire_date TEXT
);

-- Insert employee data
INSERT INTO employees (id, name, department, salary, hire_date)
VALUES
    (1, 'Alice Chen', 'Engineering', 95000, '2020-03-15'),
    (2, 'Bob Martinez', 'Engineering', 88000, '2021-06-01'),
    (3, 'Carol White', 'Marketing', 72000, '2019-11-20'),
    (4, 'David Kim', 'Marketing', 68000, '2022-01-10'),
    (5, 'Eva Brown', 'Sales', 65000, '2023-04-05'),
    (6, 'Frank Lee', 'Sales', 71000, '2020-08-22'),
    (7, 'Grace Patel', 'Engineering', 102000, '2018-07-14'),
    (8, 'Henry Wilson', 'Marketing', 78000, '2021-09-30');

-- Find salary extremes
SELECT
    MIN(salary) AS lowest_salary,
    MAX(salary) AS highest_salary,
    MAX(salary) - MIN(salary) AS salary_range
FROM employees;

-- Find the earliest and latest hire dates
SELECT
    MIN(hire_date) AS first_hire,
    MAX(hire_date) AS most_recent_hire
FROM employees;

MIN and MAX work on numbers, text (alphabetical order), and dates. They're useful for finding boundaries in your data.

GROUP BY

Aggregate functions become truly powerful when combined with GROUP BY. Instead of summarizing the entire table, you can summarize data for each group independently.

-- Create a sales table
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    product TEXT NOT NULL,
    category TEXT NOT NULL,
    quantity INTEGER,
    price REAL,
    sale_date TEXT
);

INSERT INTO sales (id, product, category, quantity, price, sale_date)
VALUES
    (1, 'Laptop', 'Electronics', 2, 999.99, '2025-01-10'),
    (2, 'Mouse', 'Electronics', 10, 24.99, '2025-01-11'),
    (3, 'Desk', 'Furniture', 3, 299.99, '2025-01-12'),
    (4, 'Chair', 'Furniture', 5, 199.99, '2025-01-13'),
    (5, 'Keyboard', 'Electronics', 8, 79.99, '2025-01-14'),
    (6, 'Monitor', 'Electronics', 4, 349.99, '2025-01-15'),
    (7, 'Lamp', 'Furniture', 12, 49.99, '2025-01-16'),
    (8, 'Headphones', 'Electronics', 6, 149.99, '2025-01-17');

-- Count products and calculate stats per category
SELECT
    category,
    COUNT(*) AS product_count,
    SUM(quantity) AS total_quantity,
    ROUND(AVG(price), 2) AS avg_price,
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive
FROM sales
GROUP BY category;

The rule is simple: every column in your SELECT that is not inside an aggregate function must appear in the GROUP BY clause.

Filtering Groups with HAVING

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. This distinction is critical.

-- Create an orders table
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT NOT NULL,
    product TEXT NOT NULL,
    amount REAL,
    order_date TEXT
);

INSERT INTO orders (id, customer, product, amount, order_date)
VALUES
    (1, 'Alice', 'Laptop', 999.99, '2025-01-01'),
    (2, 'Alice', 'Mouse', 24.99, '2025-01-05'),
    (3, 'Alice', 'Keyboard', 79.99, '2025-01-10'),
    (4, 'Bob', 'Monitor', 349.99, '2025-01-02'),
    (5, 'Bob', 'Desk', 299.99, '2025-01-08'),
    (6, 'Carol', 'Chair', 199.99, '2025-01-03'),
    (7, 'Carol', 'Lamp', 49.99, '2025-01-06'),
    (8, 'Carol', 'Mouse', 24.99, '2025-01-09'),
    (9, 'Carol', 'Keyboard', 79.99, '2025-01-12'),
    (10, 'David', 'Laptop', 999.99, '2025-01-04');

-- Customers who placed more than 2 orders
SELECT
    customer,
    COUNT(*) AS order_count,
    ROUND(SUM(amount), 2) AS total_spent
FROM orders
GROUP BY customer
HAVING COUNT(*) > 2
ORDER BY total_spent DESC;

-- Customers who spent more than $500 total
SELECT
    customer,
    COUNT(*) AS order_count,
    ROUND(SUM(amount), 2) AS total_spent
FROM orders
GROUP BY customer
HAVING SUM(amount) > 500
ORDER BY total_spent DESC;

Think of it this way: WHERE happens before GROUP BY, and HAVING happens after. You cannot use aggregate functions in WHERE, but you can in HAVING.

Combining Aggregates with JOINs

Aggregate functions truly shine when you combine them with JOINs to summarize data across related tables.

-- Create departments table
CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    budget INTEGER
);

-- Create employees table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INTEGER,
    salary INTEGER
);

-- Insert departments
INSERT INTO departments (id, name, budget)
VALUES
    (1, 'Engineering', 500000),
    (2, 'Marketing', 200000),
    (3, 'Sales', 300000),
    (4, 'HR', 150000);

-- Insert employees
INSERT INTO employees (id, name, department_id, salary)
VALUES
    (1, 'Alice', 1, 95000),
    (2, 'Bob', 1, 88000),
    (3, 'Carol', 2, 72000),
    (4, 'David', 2, 68000),
    (5, 'Eva', 3, 65000),
    (6, 'Frank', 3, 71000),
    (7, 'Grace', 1, 102000),
    (8, 'Henry', 2, 78000);

-- Department summary with headcount and salary stats
SELECT
    d.name AS department,
    d.budget,
    COUNT(e.id) AS employee_count,
    SUM(e.salary) AS total_salaries,
    ROUND(AVG(e.salary), 0) AS avg_salary,
    d.budget - SUM(e.salary) AS budget_remaining
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.name, d.budget
HAVING COUNT(e.id) > 0
ORDER BY avg_salary DESC;

Notice we used LEFT JOIN to include all departments and then HAVING to filter out departments with no employees. This combination of JOINs, GROUP BY, and HAVING is extremely common in real-world queries.

Grouping by Multiple Columns

You can group by more than one column to get more granular summaries.

-- Create a detailed sales table
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    region TEXT NOT NULL,
    category TEXT NOT NULL,
    product TEXT NOT NULL,
    revenue REAL,
    sale_month TEXT
);

INSERT INTO sales (id, region, category, product, revenue, sale_month)
VALUES
    (1, 'North', 'Electronics', 'Laptop', 2999.97, '2025-01'),
    (2, 'North', 'Electronics', 'Mouse', 249.90, '2025-01'),
    (3, 'North', 'Furniture', 'Desk', 899.97, '2025-01'),
    (4, 'South', 'Electronics', 'Laptop', 1999.98, '2025-01'),
    (5, 'South', 'Furniture', 'Chair', 999.95, '2025-01'),
    (6, 'North', 'Electronics', 'Laptop', 3999.96, '2025-02'),
    (7, 'North', 'Furniture', 'Desk', 599.98, '2025-02'),
    (8, 'South', 'Electronics', 'Monitor', 1399.96, '2025-02'),
    (9, 'South', 'Furniture', 'Chair', 599.97, '2025-02');

-- Revenue breakdown by region and category
SELECT
    region,
    category,
    COUNT(*) AS num_sales,
    ROUND(SUM(revenue), 2) AS total_revenue
FROM sales
GROUP BY region, category
ORDER BY region, total_revenue DESC;

-- Monthly totals by region
SELECT
    sale_month,
    region,
    ROUND(SUM(revenue), 2) AS monthly_revenue
FROM sales
GROUP BY sale_month, region
ORDER BY sale_month, region;

Practice Exercises

Try these on your own:

Find the total revenue per product category
List customers who have placed exactly 2 orders
Find the department with the highest average salary
Calculate the total and average order amount per month
Find categories where the average price is greater than $100

Key Takeaways

COUNT, SUM, AVG, MIN, and MAX summarize data across rows
GROUP BY splits rows into groups and applies aggregates to each group
Every non-aggregate column in SELECT must appear in GROUP BY
WHERE filters rows before grouping; HAVING filters groups after aggregation
Combine aggregates with JOINs to summarize data across related tables
Use ROUND() to control decimal precision in results

Pro Tip: When debugging a GROUP BY query, start by writing the query without aggregates to see the raw data. Then add GROUP BY and aggregates one at a time to make sure each step produces the results you expect.

Next Steps

Now that you can summarize and group data, you're ready to learn about subqueries — writing queries inside other queries for even more powerful data retrieval!