Stop 04 · SQL & DatabasesTopics · 2/6

Joins & Aggregations

In short

Real answers need data from several tables at once, rolled up into totals. Joins combine the tables, GROUP BY summarises them. Together they answer most business questions.

Combining rows across tables (INNER, LEFT, RIGHT, FULL) and summarizing them with GROUP BY and aggregate functions — how you build a single clean training dataset out of multiple normalized tables.

The mascot at a decorated wedding hall entrance matches a long guest list against a pile of reply cards.

Think of it like this

The wedding guest list

One list has everyone invited, another has the RSVPs. Match them by phone number and you see who's coming. Keep every invited guest even without an RSVP, and that's a LEFT JOIN. Then count guests per family side: that's GROUP BY.

Watch it happen

customers LEFT JOIN orders

Asha: 3

Ravi: NULL

Meena: 1

Kabir: 2

1/5Four customers, each matched to their orders by customer_id. The number is how many orders they placed.

The big ideas

What you need to know

Join on keys

Match rows using a shared column, like customer_id. Check the key is unique on one side.

INNER vs LEFT

INNER keeps only matches. LEFT keeps every row from the left table and fills gaps with NULL.

GROUP BY rolls up

COUNT, SUM, AVG, MIN and MAX summarise each group into one row.

WHERE vs HAVING

WHERE filters rows before grouping. HAVING filters groups after, like 'cities with 100+ orders'.

See it in code

Walk through the code

sql

1SELECT c.city,2       COUNT(o.order_id)        AS orders,3       ROUND(AVG(o.amount), 0)  AS avg_amount4FROM customers c5LEFT JOIN orders o6       ON o.customer_id = c.customer_id7GROUP BY c.city8HAVING COUNT(o.order_id) >= 1009ORDER BY orders DESC;

1/4Join customers to their orders. LEFT JOIN keeps customers who never ordered.

Orders and average order value per city, keeping cities only if they have at least 100 orders.

Save yourself the pain

Common mistakes

Oops

Joining on a column that isn't unique, then wondering why totals doubled.

Do this instead

Check keys first with COUNT(*) vs COUNT(DISTINCT key). Duplicate keys multiply rows.

Oops

Putting a condition on the right table in WHERE after a LEFT JOIN.

Do this instead

That quietly turns it into an INNER JOIN. Put such conditions in the ON clause instead.

Your mission

Join two tables

~30 min

Quick check

You want total revenue per city, but only for orders above ₹200. Where does 'amount > 200' go?

Go deeper

1Practice a LEFT JOIN to find rows in one table with no match in another.
2Write a query grouping and aggregating data across a joined multi-table dataset.
3Learn why WHERE can't filter on an aggregated value, but HAVING can.