VisaSpark
Sign in
beginner28 min

Aggregating and Combining Data: GROUP BY and JOIN

Summarizing rows into totals and counts with GROUP BY, and pulling related data together across tables with JOIN.

What you'll learn

  • Combine rows from two related tables using JOIN
  • Summarize groups of rows using GROUP BY with aggregate functions like COUNT and SUM
  • Use LEFT JOIN to include rows that have no match in the related table

Prerequisites

Explanation

Two ideas finish off the core SQL toolkit: pulling related tables together, and summarizing many rows into fewer, more meaningful ones.

JOIN combines rows from two tables using a matching key. You already know books.author_id points at authors.id — a JOIN uses exactly that relationship to stitch a book together with its author's details in one result row:

SELECT books.title, authors.name AS author, authors.country
FROM books
JOIN authors ON books.author_id = authors.id;

This is called an inner join: it only returns books that do have a matching author (which, given the foreign key, is every book). Picture two overlapping circles — one for books, one for authors — and a plain JOIN keeps only the overlap.

LEFT JOIN keeps every row from the first table, even without a match. If you wanted every book listed alongside its total quantity ordered, an ordinary JOIN would silently drop any book that has never been ordered, because there's no matching orders row to pair it with. LEFT JOIN fixes that: it keeps every books row regardless, filling in NULL for any orders columns when there's no match.

GROUP BY collapses many rows into one row per group, almost always paired with an aggregate function that summarizes each group:

  • COUNT(...) — how many rows are in the group
  • SUM(...) — the total of a numeric column across the group
  • AVG(...), MIN(...), MAX(...) — average, smallest, and largest values

For example, counting how many books each author has written groups the joined rows by author:

SELECT authors.name AS author_name, COUNT(books.id) AS book_count
FROM authors
JOIN books ON books.author_id = authors.id
GROUP BY authors.id;

Combining LEFT JOIN with GROUP BY and SUM is how you answer "total quantity ordered per book, including books with zero orders" — the LEFT JOIN keeps books with no orders in the result at all, and COALESCE(SUM(orders.quantity), 0) turns their otherwise-NULL total into a plain 0.

JOIN and GROUP BY are where SQL stops feeling like a filter and starts feeling like a reporting tool — the same two ideas power almost every dashboard, summary, and analytics query you'll ever write.

Inner join vs. left join, as overlapping sets

Picture two circles: 'books' and 'authors', overlapping wherever books.author_id matches authors.id. A plain JOIN keeps only the overlapping region. A LEFT JOIN keeps the entire 'books' circle, filling in NULL for author columns on any book that (hypothetically) had no matching author.

Example

Join books to authors to find every book written by an author from Japan.

SELECT books.title, authors.name AS author, authors.country
FROM books
JOIN authors ON books.author_id = authors.id
WHERE authors.country = 'Japan';

Try it yourself

Change the country to another value, like 'Nigeria', then press Run to see a different author's books.

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Guided exercise

Guided exercise

Complete the query to return each author's name along with how many books they have written, by joining authors to books and grouping by author.

Checks: Returns each author's name with their correct book count

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Stuck? Get a hint.

Independent exercise

Independent exercise

Write a query that returns every book's title along with the total quantity ordered (0 for books with no orders), using a LEFT JOIN and GROUP BY, sorted by total quantity descending and then by book id ascending as a tiebreaker (order matters for this check).

Checks: Returns every book with its total ordered quantity, including zeros, correctly sorted

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Stuck? Get a hint.

Common mistakes

  • Using a plain JOIN when rows without a match need to be included — silently dropping unmatched rows instead of using LEFT JOIN.
  • Selecting a column that isn't part of an aggregate or the GROUP BY key, expecting SQL to somehow guess which row's value to show.
  • Forgetting COALESCE (or similar) around SUM/AVG when some groups may have no matching rows at all, leaving NULL where 0 was expected.
  • Not adding a tiebreaker column to ORDER BY when several groups share the same aggregate value, leaving the order among ties unpredictable.

Knowledge check

Knowledge check

1. What does JOIN do?
2. Why would you use LEFT JOIN instead of a plain JOIN?
3. What does GROUP BY do when paired with COUNT(...)?
4. Why might COALESCE(SUM(orders.quantity), 0) be necessary after a LEFT JOIN?

Takeaway

JOIN stitches related tables together through their keys, and GROUP BY with an aggregate function turns many rows into one meaningful summary row per group.

Summary

JOIN combines rows across tables using a matching key, while LEFT JOIN preserves unmatched rows from the first table instead of dropping them. GROUP BY collapses rows into groups, summarized with aggregate functions like COUNT and SUM, forming the basis of most reporting-style queries.

References

Your notes

Notes save automatically.

Finished this lesson?

Mark it complete to track your progress and schedule a future review.

Next: AI vs Machine Learning vs Deep Learning vs Generative AI →