--- title: "SQL: Joining Tables and Summarising Groups" description: "Pulling rows from two tables together on a matching key, and collapsing many rows into one answer with COUNT, SUM and GROUP BY. This is the payoff for having split the tables in the first place." canonical: https://lightmysky.com/learn/computing/sql-joining-tables-and-summarising-groups-mt_0QPZDE2KuS source: https://lightmysky.com/learn/computing/sql-joining-tables-and-summarising-groups-mt_0QPZDE2KuS.md retrieved: 2026-11-12 --- > **Agent view.** This is the Markdown twin of the page, for tools and assistants. > When to use this site, and the call that answers each job: https://lightmysky.com/agent-instructions.md > API description (OpenAPI 3.1): https://lightmysky.com/openapi.json · Authentication: https://lightmysky.com/auth.md > Pricing: https://lightmysky.com/pricing.md · Catalog: https://lightmysky.com/llms.txt · Full catalog: https://lightmysky.com/llms-full.txt > Every machine-readable file on this domain: https://lightmysky.com/.well-known/ai-catalog.json > Ask for Markdown with `Accept: text/markdown`, a `.md` address, or `?mode=agent`. # SQL: Joining Tables and Summarising Groups Pulling rows from two tables together on a matching key, and collapsing many rows into one answer with COUNT, SUM and GROUP BY. This is the payoff for having split the tables in the first place. Subject: Computing · Area: Computer Systems · Ages 17 to 18 Page: https://lightmysky.com/learn/computing/sql-joining-tables-and-summarising-groups-mt_0QPZDE2KuS ## Ready when they can - Join two tables on a foreign key and say which rows the join drops - Count or total rows per group and read the result table - Predict the size of an unfiltered CROSS JOIN and explain how a matching condition changes its pairs ## Lesson: Joining tables and counting the groups Normalisation separated facts into tables. SQL can combine them again. These examples use SQLite. Members.member_id is a primary key; Loans.member_id refers to it. A join applies its condition rather than automatically following a foreign key. INNER JOIN returns matching pairs and omits unmatched rows. Equality with NULL is not true, so a loan with NULL member_id does not match a member. **Example.** Members contains IDs 11, 12 and 13, named Imani, Leo and Noor. Loans 41 and 42 belong to 11; loan 43 belongs to 12. SELECT Members.name, Loans.loan_id FROM Members INNER JOIN Loans ON Members.member_id = Loans.member_id; returns Imani with 41 and 42, and Leo with 43. Noor has no match. Table.column identifies which table supplies a column. *(drawing: Each cell shows one joined row. Imani appears twice because two loans match her member ID.)* SELECT Members.member_id, COUNT(*) FROM Members INNER JOIN Loans ON Members.member_id = Loans.member_id GROUP BY Members.member_id; returns 11 with 2 and 12 with 1. GROUP BY makes one group per member ID. Names may be shared, so group by ID. COUNT(*) counts rows; COUNT(column) counts non-NULL values. SUM(fine) adds non-NULL fines per group. For fines 3, 4 and 4, the count is 3 and the sum is 11. LEFT JOIN keeps every row from its left table. Unmatched members get NULL loan fields. SELECT Members.member_id, COUNT(Loans.loan_id) FROM Members LEFT JOIN Loans ON Members.member_id = Loans.member_id GROUP BY Members.member_id; includes Noor with zero. Actual loan_id values are non-NULL primary keys. COUNT(Loans.loan_id) ignores an unmatched row's NULL; COUNT(*) would count that row as one. *(drawing: Members LEFT JOIN Loans with COUNT(Loans.loan_id) retains Noor's member ID 13 with zero loans.)* SELECT * FROM Members CROSS JOIN Loans; requests all columns and every possible pair. With m and n input rows, this unfiltered CROSS JOIN returns m times n rows. Three members and four loans give 12 pairs. A matching condition can remove unwanted pairs. CROSS JOIN can also be intentional. A large result alone does not prove a condition was forgotten. **Recap.** Choose a join condition, group by member ID, and count the values your question needs. *SQL lesson, questions and study cards written for this dataset. The query examples use SQLite.* ## Practice 8 questions on this page, each with its working shown. ## Needs first - [SQL: Selecting, Filtering and Ordering Rows](https://lightmysky.com/learn/computing/sql-selecting-filtering-and-ordering-rows-mt_lDVVG2AKzs) ## Opens up - [Window Functions and Running Calculations](https://lightmysky.com/learn/computing/window-functions-and-running-calculations-mt_aaVxFPjWQv) - [The Relational Model and Relational Algebra](https://lightmysky.com/learn/computing/the-relational-model-and-relational-algebra-mt_unDBd_Ug8T)