SQL WHERE and HAVING: Check a Grouped Report with a Tiny Dataset

State the question and the sample rows

Ask: which regions have a paid-order total of at least 100? Use North paid 40, North paid 70, North unpaid 500, South paid 90 and South unpaid 300. These values are synthetic teaching data, not institute revenue or customer records.

Write the amount unit and the meaning of “paid” in your exercise notes. Do not include joins, dates or currency conversion in the first version. Keeping the fixture deliberately small lets another learner inspect every contribution.

Calculate the expected result on paper

Remove the unpaid rows from consideration. North then has 40 plus 70, which totals 110. South has 90. Under the stated threshold only North should appear. Record both intermediate paid totals, including South, before applying the final group filter.

This manual result is the acceptance expectation. Do not copy the query output into the expected column after execution. That would compare the query with itself rather than checking whether it answers the original question.

Translate each condition into its stage

Use WHERE to retain the rows whose status is paid. Group those retained rows by region and calculate SUM(amount). Apply HAVING to the aggregate threshold. In PostgreSQL’s documented table-expression model, row filtering precedes grouping and the group filter follows it.

Build and inspect the intermediate grouped result before adding HAVING. If the paid totals are already wrong, a later threshold cannot repair the input selection. Keep every selected field consistent with the intended grouping.

Use mutations that expose the difference

Increase an unpaid amount and predict that neither paid total changes. Next increase South’s paid amount from 90 to 120; predict that South now passes the threshold. Finally lower North’s first paid amount so that its total falls below 100.

Reset the fixture between scenarios and record each expected final set. These changes test the report’s logic more directly than merely adding more rows with the same status and amount.

Review the grouped report

• The row condition matches the stated status rule.
• Each region total uses only eligible rows.
• The aggregate threshold is checked at group level.
• Manual totals and query output agree for every fixture.
• Unpaid amounts do not influence paid-only totals.

Keep the scope of the result clear

The exercise intentionally avoids NULL amounts, duplicate rows and joins; those need separate rules in a real report. Keep this fixture as a focused regression case when extending the query. Mango Engineers’ SQL course covers grouping and HAVING; confirm the current practical sequence when discussing your learning path.