Rullst AcademyRullst Academy/Lesson
PTENES
Databases and SQL I · Lesson 08 / 12
My files and GitHubStudent guide

Summarize and count carefully

Use GROUP BY, COUNT, SUM, COALESCE and HAVING without hiding empty groups.

Learning paths18 min

Let's discover together

  1. 1

    GROUP BY gathers rows for a material summary. Group by identity; label alone would combine both Orbit records. Group displayed nonaggregated columns to avoid depending on arbitrary values.

  2. 2

    COUNT(*) counts rows, including a LEFT JOIN’s preserved unmatched row. COUNT(l.id) ignores NULL and counts related loans. SUM(l.units) adds units, unlike counting loans.

  3. 3

    SUM without values returns NULL in SQLite. COALESCE(SUM(l.units),0) uses zero for no open loans. This interpretation belongs to this report; do not automatically turn every unknown into zero.

  4. 4

    WHERE filters rows before grouping; HAVING filters groups. Check the full report before requesting availability at or below zero. Preserve negative numbers: they can reveal inconsistency.

Your data workbench

Our model: kits.id ← loans.kit_id. One material can have many loans; names may repeat. We use fictional data only.

Predict before opening the reference. It shows verified output from the original example, not execution of your text. Run your changes with the kit on your computer.

-- Summarize and count carefully
SELECT k.id, COUNT(l.id) AS open_loans, COALESCE(SUM(l.units),0) AS open_units FROM kits AS k LEFT JOIN loans AS l ON l.kit_id=k.id AND l.returned IS NULL GROUP BY k.id ORDER BY k.id;
Compare with reference output
[
  {
    "columns": [
      "id",
      "open_loans",
      "open_units"
    ],
    "rows": [
      [
        1,
        1,
        1
      ],
      [
        2,
        1,
        2
      ],
      [
        3,
        1,
        1
      ],
      [
        4,
        0,
        0
      ]
    ]
  }
]

Run the command in the extracted folder’s terminal. Use python3 on macOS/Ubuntu or py if that is your Windows launcher. Every run starts in memory; read README.md for saving files and recovery practice.

python lab.py run lesson.sql
My files and GitHub

The editor saves your .sql draft in this browser. Download it and replace lesson.sql in the kit; for the final project, availability.sql. The ZIP remains the original template. GitHub publishing sends only the confirmed file, without executing SQL or sending a database.

Official links and next step

✦
YOUR MISSION

Use GROUP BY, COUNT, SUM, COALESCE and HAVING without hiding empty groups.

Now it's your turn

Discovery workshop

Read, predict and check

Use GROUP BY, COUNT, SUM, COALESCE and HAVING without hiding empty groups.
Build your answers and check when you are ready.
A moment to understand

Which expression counts loans in a LEFT JOIN?

I need a hint

COUNT(*) counts rows, including a LEFT JOIN’s preserved unmatched row. COUNT(l.id) ignores NULL and counts related loans. SUM(l.units) adds units, unlike counting loans.

Loading your journey…

Take it with you

Compare COUNT(*) and COUNT(l.id) for Nova. Then select groups with more than one open unit and explain each row.

At your own pace

My files and GitHub

Every lesson can become your own record: code, notes or a practice plan. Saving a file to GitHub is optional and does not change your XP.

Student guide

The editor and downloads open when this lesson is available to your account. Check access below or return to the track map.

Checking your access to this lesson…

Your path, step by step
Previous lessonLearning paths

Rullstpetbot

AI can make mistakes. Check the lesson material.