SQL for Data Analytics · Lesson 2 of 3

SQL Joins, Grouping, and Aggregation

Combine related tables and aggregate at the correct row grain without inflating totals.

Lesson

1. Introduction

Analytical questions often need columns spread across tables. A join connects related rows, while grouping summarizes multiple records. The main risk is changing the number of rows unintentionally and then reporting incorrect totals.

2. Understand the concept

An INNER JOIN keeps matching rows; a LEFT JOIN keeps every left-side row and fills unmatched right-side values with NULL. GROUP BY creates one output row per group. COUNT(*) counts rows; COUNT(DISTINCT key) counts unique entities.

3. Syntax and interpretation

SELECT c.region, SUM(o.amount) AS revenue,
       COUNT(DISTINCT o.order_id) AS order_count
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.region;

This assumes each customer ID maps to exactly one customer row. If the dimension has duplicates, the join can multiply order amounts.

4. Apply it to an analysis

Combine related tables and aggregate at the correct row grain without inflating totals. Start with a small, known example and state what each row or value represents. After the transformation, check row counts, types, keys, and units against the original data. If an assumption fails, stop and investigate instead of presenting the output as a valid result.

5. Common mistakes

Using WHERE to filter an aggregate result instead of HAVING.
Joining a one-to-many table and interpreting repeated facts as separate orders.

6. Practice task

A table contains multiple items per order. How do you count orders by region after joining to a region table?

7. One possible solution

Group by region and use COUNT(DISTINCT order_id), after validating that the region join does not create unexpected duplicates.

8. Summary and next step

Combine related tables and aggregate at the correct row grain without inflating totals. Continue to the next course section and apply this foundation to a different analytics tool or workflow.

Visual guide

Working through SQL Joins, Grouping, and Aggregation

Step 01 Define
State the question, data grain, and expected output.
Step 02 Apply
Use the method and check its assumptions on a small example.
Step 03 Verify
Reconcile the result and communicate limitations.

Code examples

Keep customers with no orders

SQL

SELECT c.customer_id, COUNT(o.order_id) AS orders\nFROM customers AS c\nLEFT JOIN orders AS o ON o.customer_id = c.customer_id\nGROUP BY c.customer_id;

Expected output

Includes customers with zero matching orders.

Filter groups after aggregation

SQL

SELECT region, SUM(amount) AS revenue\nFROM orders\nGROUP BY region\nHAVING SUM(amount) > 10000;