Skip to content
FORGENO ADS GAMESUnlock all $8

Data Lab · Unit 7 of 8

Unit 7: SQL summaries and joins

COUNT, SUM and AVG, grouping with GROUP BY and HAVING, and joining tables to answer real business questions.

Opens the first lesson of this unit in Data Lab. Every lesson is open from the start.

Lesson 1: Counting and totaling: aggregate functions

Goal: Summarize a whole table with COUNT, SUM, AVG, MIN and MAX.

Aggregate functions turn many rows into one summary value, just like SUM and AVERAGE in a spreadsheet.

FunctionReturns
COUNT(*)the number of rows
COUNT(column)the number of rows where that column is not empty (not NULL)
SUM(column)the total
AVG(column)the mean
MIN(column) / MAX(column)the smallest / largest
SELECT COUNT(*) AS num_orders,
       SUM(qty) AS items_sold
FROM orders;

In our shop that returns 30 orders and 57 items. You can combine aggregates with WHERE: SELECT COUNT(*) FROM orders WHERE order_date >= '2025-07-01'; counts orders from July onwards. WHERE filters first; then the aggregate summarizes what is left.

NULL means a value is missing, which is different from zero or an empty text. Aggregates like SUM, AVG and COUNT(column) skip NULLs, much like spreadsheet functions skip empty cells.

Hands-on lab in the room: SQL lab. Summarize the shop with aggregate functions.

Aggregate function
A function such as COUNT, SUM or AVG that combines many rows into one value.
NULL
SQL's marker for a missing value. It is not zero and not empty text.

Remember: COUNT, SUM, AVG, MIN, MAX turn many rows into one number. WHERE filters before they summarize.

Lesson 2: GROUP BY: summaries for each group

Goal: Group rows and summarize each group, then filter groups with HAVING.

GROUP BY splits rows into groups that share a value, then runs the aggregate on each group. It is SQL's pivot table.

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city;
Result
citycustomers
Hillcrest3
Lakeside3
Riverton4

The rule: every column in SELECT must either be in the GROUP BY or be inside an aggregate. "City" is the group; COUNT(*) summarizes each group.

To filter groups (not rows), use HAVING, which comes after GROUP BY:

SELECT product_id, SUM(qty) AS sold
FROM orders
GROUP BY product_id
HAVING SUM(qty) >= 10;

WHERE filters rows before grouping. HAVING filters groups after the aggregates are calculated. A common combination: WHERE to pick a time period, GROUP BY to split, HAVING to keep the big groups, ORDER BY to rank.

Hands-on lab in the room: SQL lab. Group the shop's data.

GROUP BY
Splits rows into groups by a column's values so aggregates are calculated for each group.
HAVING
Filters groups after aggregation, such as keeping only groups with a total over 10.

Remember: GROUP BY = one summary row per group. WHERE filters rows before; HAVING filters groups after.

Lesson 3: JOIN: combining tables

Goal: Join tables on matching keys to answer questions across them.

Why three tables instead of one big sheet? If every order repeated the customer's name and city and the product's name and price, the same facts would be copied hundreds of times, and a single change, like a new price, would need hundreds of edits. Instead each fact is stored once, and tables point at each other with IDs.

In orders, customer_id points to customers.id, and product_id points to products.id. Each table's id is its primary key (unique for every row). A column that points to another table's key is a foreign key. A JOIN matches them back up:

SELECT orders.id, customers.name, orders.qty
FROM orders
JOIN customers ON orders.customer_id = customers.id;

Read it as: take each order, find the customer whose id equals the order's customer_id, and put them side by side. Write table.column whenever two tables have a column with the same name, like id and name.

A plain JOIN (also called INNER JOIN) keeps only rows that match in both tables. A LEFT JOIN keeps every row from the first table, even with no match, filling the gaps with NULL. That is how you find, for example, customers who have never ordered: Jade Wilson in our shop.

SELECT customers.name
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
WHERE orders.id IS NULL;

Hands-on lab in the room: SQL lab. Join the tables to answer real questions.

Primary key
A column, often id, whose value is unique for every row in a table.
Foreign key
A column that holds another table's primary key, linking the two tables.
JOIN
Combines rows from two tables where a condition, usually matching keys, is true.

Remember: JOIN ... ON foreign_key = primary_key. INNER keeps matches only; LEFT keeps every row from the first table.

Lesson 4: Answering a real business question

Goal: Combine joins, grouping and sorting in one query.

Real questions usually need several pieces at once. Here is the full pattern, built up step by step.

Question: which products brought in the most money?

Worked example: building the query

  1. Revenue needs price (in products) and quantity (in orders), so JOIN them on the product key.
  2. Revenue per order line is products.price * orders.qty.
  3. We want one row per product, so GROUP BY the product name and SUM the revenue.
  4. Rank them: ORDER BY revenue DESC.
SELECT products.name,
       SUM(products.price * orders.qty) AS revenue
FROM orders
JOIN products ON orders.product_id = products.id
GROUP BY products.name
ORDER BY revenue DESC;

In our shop the Backpack leads with 175.00 (5 sold at 35.00), followed by Desk lamp with 84.00.

Notice that the most popular item by quantity is not the biggest earner. Notebooks sell the most units (20) but bring in only 70.00. Deciding what "best" means is part of the question.

Hands-on lab in the room: SQL lab. Answer business questions with full queries.

When a query gets long, build it in steps and run it after each step to check the intermediate result. Professionals do this too.

Remember: Big question? Build the query in steps: join, check, group, check, sort.

Practise it in Data Lab

Each lesson ends with two or three questions that check you can use the idea, not just recognise it. Wrong answers explain why and come back in Review. The unit ends with a 6-question quiz; pass it with 80% or more to master the unit. Hands-on lab: SQL lab.

Every lesson is free: no ads, no lives and no paywall.