Database Management · Beginner level

Beginner level · Module 4: Aggregation and Reporting Queries · Lesson 3 of 3

Building Report Queries

Combine joins, grouping, CASE and date functions into report queries that answer management questions.

In this lesson you will

  • Use CASE to classify rows
  • Group by month and build a complete report query

Key concepts

CASE WHENgrouping by monthreport columnschecking results

The lesson

CASE classifies values: CASE WHEN balance = 0 THEN 'Paid' WHEN balance < fee THEN 'Part paid' ELSE 'Not paid' END. Grouping by that expression counts students in each status.

To group by month, extract it from the date: strftime('%Y-%m', paid_on) in SQLite or to_char(paid_on, 'YYYY-MM') in PostgreSQL. Order months so the report reads chronologically.

Build reports step by step: start with the join, check the rows, then add grouping and calculations. Check totals against a different method, such as the overall SUM.

Step by step

  1. Write a CASE expression for payment status.
  2. Count students per status.
  3. Total collections per month.
  4. Check that monthly totals add up to the overall total.

Practice activity

Produce the bursar's monthly report from the practical activity.

Expected result: Three report queries whose figures reconcile with each other and with hand checks.

Check your understanding

  1. What does a CASE expression do?

    Show answer

    Returns different values depending on conditions.

  2. How can you check a monthly report's totals?

    Show answer

    Compare the sum of the months with the overall total.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.