MariaDB / Basic: relational foundations / Lesson 5

Joins, counts and grouped reports

Build a customer report without losing empty groups or multiplying totals. Understand the grain of every intermediate relation.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Predict inner and outer join results.
Separate row filtering from aggregate filtering.

Ask what one row represents

Before joining, write the grain of each relation: one row per customer, one row per booking, or one row per booking line. A one-to-many join repeats the parent once for every matching child. That repetition is useful for detail reports but can inflate a parent-level amount when summed. INNER JOIN keeps matches; LEFT JOIN also preserves unmatched left rows with NULL child columns. Neither join automatically knows the business grain of a measure.

Count a child key, not the preserved row

This complete derived-data query contains two customers and two bookings for customer 1. LEFT JOIN preserves customer 2. COUNT(b.booking_id) ignores the NULL child key and therefore reports zero; COUNT(*) would count the preserved outer row as one. Both customer columns are included in GROUP BY for clear deterministic grouping and compatibility with ONLY_FULL_GROUP_BY. An aggregate report should make its grouping rule explicit even if a permissive SQL mode accepts less precise SQL.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT c.id, c.name, COUNT(b.booking_id) AS bookings
FROM (SELECT 1 AS id, 'Lina' AS name UNION ALL SELECT 2, 'Omar') AS c
LEFT JOIN (SELECT 11 AS booking_id, 1 AS customer_id
           UNION ALL SELECT 12, 1) AS b ON b.customer_id = c.id
GROUP BY c.id, c.name ORDER BY c.id;

Expected output

id | name | bookings
1  | Lina | 2
2  | Omar | 0

The unmatched customer remains visible with a zero child count.

Keep optional filters in the correct place

For a report of all customers and their paid bookings, put the paid condition in the join ON clause. Moving b.state = paid into WHERE removes unmatched customers because NULL does not equal paid, effectively changing the report. WHERE filters rows before grouping. HAVING filters the grouped result, such as customers with at least two matching bookings. Think through the empty group and one-match cases before deciding that a query is correct.

Aggregate before joining another many side

Suppose a booking has two lines and three payments. Joining all three tables directly creates six combinations, so both line totals and payment totals can repeat. First aggregate each many-side relation to one row per booking, then join those summaries to bookings. The example groups synthetic payment rows by booking and filters aggregate totals over forty. It yields booking 10 with fifty. COALESCE can replace a missing joined total with zero when zero has the intended business meaning.

sql Illustrative SQL; expected results are reasoned, not measured.

SELECT booking_id, SUM(amount) AS paid
FROM (SELECT 10 AS booking_id, 20 AS amount
      UNION ALL SELECT 10, 30
      UNION ALL SELECT 20, 5) AS p
GROUP BY booking_id HAVING SUM(amount) > 40
ORDER BY booking_id;

Expected output

booking_id | paid
10         | 50

HAVING is evaluated for grouped totals; WHERE amount > 40 would answer another question.

Explain duplicates before removing them

DISTINCT removes duplicate projected rows; it does not repair an incorrect join relationship or inflated SUM. Check the join keys and constraints first. SUM over no input rows returns NULL, whereas COUNT returns zero, so report presentation may need an explicit default. Use stable ORDER BY for exported reports, and document whether cancelled bookings, refunds and missing customers are included. These are reporting semantics that an optimizer cannot decide for you.

Common errors and fixes

Totals increase after adding payments to a report

Why it happens: Two one-to-many relationships multiplied each other.
How to fix it: Preaggregate both child sets to the common booking grain.
Prevent it next time: Write the grain beside each join and verify small cardinalities.

Customers with no bookings are counted as one

Why it happens: COUNT(*) counted the LEFT JOIN preserved row.
How to fix it: Count a non-NULL child key instead.
Prevent it next time: Include an unmatched parent in every outer-join fixture.

ERROR 1055: column is not in GROUP BY

Why it happens: A selected nonaggregate column lacks a clear grouping rule.
How to fix it: Group the intended dimensions or aggregate with a justified rule.
Prevent it next time: Develop with deliberate SQL modes and avoid arbitrary representative values.

Check your understanding

1. Customer 2 has no paid bookings. Where should a paid-state predicate go if customer 2 must remain with count zero?

Show worked solution

Place it in LEFT JOIN ... ON together with the customer-key equality, then COUNT the child booking key. WHERE on the child state would discard the NULL-extended row. Keep any mandatory customer-level scope in WHERE, because those predicates intentionally decide which customers belong in the report.

2. A booking has line amounts 10 and 15 and payments 5, 8 and 12. What should a correct summary report show?