TL;DR

Learn to summarize data with aggregate functions like COUNT, SUM, AVG, and GROUP BY

Key concepts

SQL GROUP BY
SQL aggregate functions
COUNT SUM AVG

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.

What You'll Learn

By the end of this lesson you will write a single query that reports total revenue per category, and you will be able to say in advance how many rows it returns and why that many. That second half is the real skill: an aggregate query's row count is the number of groups, not the number of rows you started with, and getting that wrong is how a report quietly doubles a total or loses a category.

This is the milestone the warehouse's revenue rollups stand on — the capstone summarises orders by category and by month, and every number in those summaries comes out of a GROUP BY. You arrive here able to read rows one at a time and filter and sort them (Filtering and Sorting); what is new is collapsing many rows into one and deciding what that one row is allowed to say.

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, in ONE pass over the table rather than three.
-- Aggregates in the same SELECT share one scan, so ask for every
-- number you need together instead of running a query per number.
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');

-- MIN and MAX beside each other, plus their difference computed here
-- rather than in whatever reads this: a range is a fact about the table,
-- so the database is the cheapest place to work it out.
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, on text, and on dates stored as text in YYYY-MM-DD form — that last one works because the format sorts correctly as a string, which is exactly why the format is worth using.

For text, be careful with the word "alphabetical". The default comparison here is by character code, not by dictionary order, so every uppercase letter sorts before every lowercase one. Over the values Apple, banana, Cherry and apple, MIN returns Apple and MAX returns banana — Cherry is not the largest, because its capital C sorts below any lowercase letter. If your text is mixed-case and you meant dictionary order, compare on LOWER(column) instead of trusting the default.

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');

-- One row per category, with every statistic the report needs on that row.
-- The shape to notice: category is the only bare column and it is the one
-- GROUP BY names, so each value in the row is a fact about the whole group.
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;

Before you read the result, predict its shape. GROUP BY does not filter rows, it collapses them: the number of rows a grouped query returns is the number of distinct values in the grouping column, not the number of rows in the table. Eight sales rows over two categories come back as two rows. That is the single most useful thing to hold about aggregation, because it is what tells you whether a report is summarising or silently double-counting.

The rule about bare columns, and what this engine actually does with it

There is a discipline worth following: every column in your SELECT should either be inside an aggregate function or named in the GROUP BY clause. Whether it is a rule your database enforces is a different question, and the one worth committing an answer to before you read on.

Predict

The query below groups three sales into two regions and names a column, rep, that is neither grouped nor aggregated. Two different reps sold into the north region, so there is no single rep the north row could honestly report. What happens when you run this on SQLite?

CREATE TABLE sales (id INTEGER PRIMARY KEY, region TEXT NOT NULL, rep TEXT NOT NULL, amount INTEGER NOT NULL);
INSERT INTO sales VALUES (1,'north','Ada',100),(2,'north','Bo',250),(3,'south','Cy',80);
SELECT region, rep, SUM(amount) AS total FROM sales GROUP BY region;

It fails with an error, because rep is neither grouped nor aggregated.
It returns two rows, and the north row shows a rep from that group beside the whole group's total.
It returns three rows, because rep is effectively added to the grouping.

Continue learning

Now run it and see.

CREATE TABLE sales (id INTEGER PRIMARY KEY, region TEXT NOT NULL, rep TEXT NOT NULL, amount INTEGER NOT NULL);
INSERT INTO sales VALUES (1,'north','Ada',100),(2,'north','Bo',250),(3,'south','Cy',80);
SELECT region, rep, SUM(amount) AS total FROM sales GROUP BY region;

It prints north | Ada | 350 — a name from one row of the group sitting beside a total that is mostly the other row's. PostgreSQL and MySQL under ONLY_FULL_GROUP_BY reject the same query outright.

So the rule is real, and the reason to keep it is not that your database will enforce it — here it will not. It is that a bare column beside an aggregate is a question with no answer, and this engine answers it anyway with a value from an unspecified row. Never depend on which one you get.

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.

That ordering is not a style preference — it is what each clause has available to it. WHERE runs while rows are still individual, so it can read amount and cannot read SUM(amount), which does not exist yet. HAVING runs once every individual row has been collapsed into a group, so it can read SUM(amount) and cannot read a single row's amount at all. Two filters, different moments, different vocabularies.

Predict

Five orders across two regions. The query filters twice: WHERE discards orders under 50, and HAVING discards regions under 100. The south region has orders of 80, 200 and 15. What revenue does south report?

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, region TEXT NOT NULL, total REAL NOT NULL);
INSERT INTO orders VALUES (1,'Ada','north',120.0),(2,'Bo','south',80.0),(3,'Ada','north',60.0),(4,'Cy','south',200.0),(5,'Bo','south',15.0);
SELECT region, SUM(total) AS revenue
FROM orders
WHERE total >= 50
GROUP BY region
HAVING SUM(total) > 100;

295.0 — all three south orders, because HAVING is what decides which regions survive.
280.0 — the 15 was discarded before grouping, so it never reached the total.
South does not appear, because one of its orders was below the WHERE threshold.

Continue learning

Putting the clauses in order

Every arrangement below runs against the orders table, which is already given.

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, region TEXT NOT NULL, total REAL NOT NULL);
INSERT INTO orders VALUES (1,'Ada','north',120.0),(2,'Bo','south',80.0),(3,'Ada','north',60.0),(4,'Cy','south',200.0),(5,'Bo','south',15.0);

Arrange the code

These five lines are one query that reports revenue per region for orders of 50 or more, keeping only the regions that clear 100. They are shuffled. Put them in the order that runs — then answer what the order is really testing: why can HAVING not trade places with WHERE, given that both of them filter?

WHERE total >= 50
GROUP BY region
FROM orders
HAVING SUM(total) > 100;
SELECT region, SUM(total) AS revenue

Continue learning

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.

One detail in that query is doing more work than it looks: the count is written over a named column rather than over everything. A LEFT JOIN keeps every department, including HR, which has no employees — so something has to decide what an empty department's headcount is, and the choice of what to count is what decides it. The block below is that choice made wrongly.

Debug

Three categories exist and Books has no products at all. This report is meant to show every category with how many products it holds and what they are worth in cents. It runs, exits cleanly, and reports Books as holding 1 product worth None. Commit a hypothesis about where that phantom product came from, then fix the query so Books reports 0 products and 0 cents.

CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE products (id INTEGER PRIMARY KEY, category_id INTEGER NOT NULL, name TEXT NOT NULL, price_cents INTEGER NOT NULL);
INSERT INTO categories VALUES (1,'Audio'),(2,'Cables'),(3,'Books');
INSERT INTO products VALUES (1,1,'Headphones',8900),(2,1,'Earbuds',4500),(3,2,'HDMI 2m',1200);
SELECT c.name AS category, COUNT(*) AS products, SUM(p.price_cents) AS total_cents
FROM categories c
LEFT JOIN products p ON p.category_id = c.id
GROUP BY c.name
ORDER BY c.name;

Expected output:

3 row(s) affected
3 row(s) affected
category | products | total_cents
---------------------------------
Audio | 2 | 13400
Books | 0 | 0
Cables | 1 | 1200

Before you change anything — why do you think this fails?Continue learning

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;

Grouping by two columns makes one group per distinct combination that appears in the data — four here, not six, because no region sold every category in every month. A grouped report never invents a row for a combination nobody sold.

Two variations to try on the fence above. Predict the row count of each before you run it.

Delete category from both the SELECT and the GROUP BY of the first query. Four rows become two, one per region, and each revenue figure is the sum of that region's categories. Removing a grouping column always merges groups, never splits them.
Add product to the GROUP BY of the first query, and to its SELECT. The four rows become six, because North Electronics splits into Laptop and Mouse. Adding a grouping column subdivides, and at the limit — grouping by something unique per row — you are back to the raw table with an aggregate that summarises one row each.

Debug

This report is meant to show, per region, how many sales reps sold there and the region's total. It runs, exits cleanly, and reports north as Ada with 350 — but Ada only sold 100 of that 350, and the north region has two reps. Commit a hypothesis about what the rep column is actually reporting, then fix the query so each region reports its number of distinct reps instead.

CREATE TABLE sales (id INTEGER PRIMARY KEY, region TEXT NOT NULL, rep TEXT NOT NULL, amount INTEGER NOT NULL);
INSERT INTO sales VALUES (1,'north','Ada',100),(2,'north','Bo',250),(3,'south','Cy',80);
SELECT region, rep, SUM(amount) AS total
FROM sales
GROUP BY region
ORDER BY region;

Expected output:

3 row(s) affected
region | reps | total
---------------------
north | 2 | 350
south | 1 | 80

Before you change anything — why do you think this fails?Continue learning

Recall

No notes. In 05-joins-and-relationships you matched orders to customers. If one customer has three orders and you join customers to orders, how many rows does that one customer produce in the result?

One row, because there is one customer.
Three rows, one per matching order.
Four rows: one for the customer plus one per order.

Continue learning

Try It Yourself

Build the category report the capstone opens with. The starter below joins categories to products and reports a count and a total, but it loses the category that sold nothing, it has no defined order, and its money column is a float.

One function you have not met yet, because you will need it: COALESCE(x, fallback) returns x unless x is NULL, in which case it returns the fallback. It is how SUM over an empty group — which is NULL, and prints as None — becomes the 0 a report should show. Null Handling covers the whole family later; this one case is worth having now.

Build

The starter reports two of the three categories. Work TODO 1 through TODO 3 so all three appear, the empty one reports 0 rather than None, and the rows come back highest total first with ties broken by category name ascending.

CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE products (id INTEGER PRIMARY KEY, category_id INTEGER NOT NULL, name TEXT NOT NULL, price_cents INTEGER NOT NULL);
INSERT INTO categories VALUES (1,'Audio'),(2,'Cables'),(3,'Books');
INSERT INTO products VALUES (1,1,'Headphones',8900),(2,1,'Earbuds',4500),(3,2,'HDMI 2m',1200);
-- TODO 1: report every category, including ones with no products.
--         Audio has 2 products; Books has 0 and must still appear.
-- TODO 2: report each category's total stock value in cents as total_cents,
--         and its product count as products. Audio is 8900 + 4500 = 13400;
--         Books must be 0 and 0, not 1 and None.
-- TODO 3: sort by total_cents descending, breaking ties by category ascending.
SELECT c.name AS category, COUNT(*) AS products, SUM(p.price_cents) AS total_cents
FROM categories c
JOIN products p ON p.category_id = c.id
GROUP BY c.name;

Expected output:

3 row(s) affected
3 row(s) affected
category | products | total_cents
---------------------------------
Audio | 2 | 13400
Cables | 1 | 1200
Books | 0 | 0

Before you run — what's your plan for the first TODO?Continue learning

The money column here is integer cents rather than a decimal, and that is deliberate. Cents are exact; a float column for money accumulates rounding error, and this engine stores every DECIMAL as a float whatever the declaration says.

Capstone milestone

Milestone — revenue rollups. The warehouse capstone opens with a per-category summary that has to list every category the shop sells, including the ones that sold nothing this month. The build task above is that rollup in miniature. Confirm you can write it without losing a row or inventing one.

Hint: You do not need the full warehouse yet — this confirms the rollup shape the capstone's monthly summary is built on. There, the same LEFT JOIN plus COALESCE pair is what keeps a quiet category visible in the report instead of silently vanishing from it.

Used LEFT JOIN so a category with no products still appears
Counted with COUNT of a named column rather than COUNT star, so an empty category reports 0
Wrapped SUM in COALESCE so an empty group prints 0 instead of None
Ordered by total descending with a second key, so tied rows have a defined order
Kept money in integer cents rather than a float column

Continue learning

Transfer

Both queries below report what each customer spent. The first groups the orders; the second asks a subquery for each distinct customer's total. Against this data they return exactly the same two rows. Which statement describes what actually differs between the two shapes?

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total INTEGER NOT NULL);
INSERT INTO orders VALUES (1,'Ada',120),(2,'Bo',80),(3,'Ada',60);

SELECT customer, SUM(total) AS spent FROM orders GROUP BY customer ORDER BY customer;

SELECT DISTINCT customer, (SELECT SUM(o2.total) FROM orders o2 WHERE o2.customer = o1.customer) AS spent
FROM orders o1 ORDER BY customer;

Nothing differs; they are two spellings of one query and will always agree.
They differ in what decides the rows: GROUP BY can only report groups that exist in the data, while the subquery form reports one row per row of whatever table drives it.
The subquery form is wrong because it can return a different total per row of the same customer.

Continue learning

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, so the row count of the result is the number of groups
Keep every non-aggregate column in the GROUP BY as a discipline: SQLite accepts a bare column and answers with a value from an unspecified row, while stricter engines refuse the query
WHERE filters rows before grouping; HAVING filters groups after aggregation
Over a LEFT JOIN, COUNT(*) counts the manufactured null row and SUM over nothing returns None — name the column and wrap it in COALESCE
ROUND() controls the arithmetic, not the display: ROUND(AVG(salary), 0) still prints 95000.0, because the result is a float

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

Aggregation answers "how much?" and "how many?" across groups. But some questions require comparing a row against an aggregated result — for example, "which employees earn more than their department's average?" The next lesson covers subqueries, which let you nest one query inside another. Subqueries unlock a new level of analytical power by using the output of one query as the input to another.

Two-tier handoff: this document is the complete reading surface. Continue learning for stateful practice, progress, and real sandbox execution.