The Life Starter
Sign up
Grade 11 · lesson 4 of 4

Count Related Records by Key

Aggregate matched records and retain objects with zero related rows.

Download the grade pack

Supplied materials

  • Paper and pencil; an adult may read aloud. No device, account or purchase is needed.

Learn the idea

Grouping collects rows with the same key so we can summarize their related records. Decide whether the result should include entities with no related rows. For this exercise we want one row per person, even when their loan count is zero. A left join from people to loans supplies that coverage.

Fictional people: P1 = Dax; P2 = Emi; P3 = Fox. Loans: L1 → P2; L2 → P1; L3 → P2. SQL-shaped plan: SELECT people.person_id, COUNT(loans.loan_id) FROM people LEFT JOIN loans ON matching person_id GROUP BY people.person_id. Loan IDs are unique and non-NULL. Counting loan_id ignores the NULL produced for a person with no loan; COUNT(*) would instead count that placeholder row.

  1. Match the three loans to their person keys.
  2. Make groups P1, P2 and P3, including an empty P3 loan group.
  3. Count non-NULL loan IDs in each group.
  4. Compare COUNT(loan_id) with COUNT(*) for P3’s left-join placeholder row.
Worked example

See the method in action

Output counts: P1 → 1; P2 → 2; P3 → 0. The counts sum to 3 actual loans. For P3, COUNT(loan_id) = 0 but COUNT(*) = 1 because its left-join placeholder row still exists.

Your turn

Check your reasoning

Your answer is temporary and is not saved to an account. Download your current answer before leaving or reloading. Written work on paper or in another app stays in your own file.

Add L4 → P3. What count row does P3 now have?

Feedback appears here after you check. This is practice, not a graded school assessment.

See the answer and explanation

P3 → 1

The new loan supplies one non-NULL loan_id in P3’s group, so its loan count becomes 1.

Take it further

Instead add L4 → P2 to the original data. Expected counts: P1 → 1, P2 → 3, P3 → 0; total counted loans = 4.

Nothing here is saved to an account or sent to a teacher. The answer download includes your current choice and any checked feedback.

Check the subject sources

These LifeStarter passages, scenarios and exercises are authored for this Academy with AI assistance and review. External resources keep their publishers’ terms. A grade label suggests a starting level; it does not establish alignment with your school’s complete requirements.