Lesson 10 of 20

In progress

Course progress

SQL

Reporting patterns that hold up

Percentages, conditional counts, month buckets and empty results: the four patterns behind most real reports.

A report is a query someone makes a decision with. That raises the bar: totals must reconcile, empty results must be explicit, and percentages must not silently divide by zero.

What you will learn

  • Build conditional counts and percentages safely.
  • Bucket dates into months.
  • Make empty results visible instead of absent.

The dataset

SQL
Editable. Runs in your browser.





Press Run to see the output.

Conditional counts

SQL
Editable. Runs in your browser.





Press Run to see the output.

Each SUM(CASE ...) is a count restricted to a condition. One pass over the table produces every column, and the numbers always add up to the total because they come from the same rows.

This is the alternative to running one query per status and hoping the totals match.

Percentages without dividing by zero

SQL
Editable. Runs in your browser.





Press Run to see the output.

Three details make this correct:

  • 100.0 forces decimal arithmetic; 100 with integer operands truncates.
  • COUNT(*) is never zero inside a group, because a group exists only if it has rows.
  • Rounding happens once, at the end, on the final percentage.

If the denominator can be zero, wrap it: ... / NULLIF(COUNT(*), 0). NULL is a better answer than an error or a silent zero.

Month buckets

SQL
Editable. Runs in your browser.





Press Run to see the output.

ISO dates stored as text make SUBSTR(paid_on, 1, 7) a month key that sorts correctly. With a real date type, DATE_TRUNC('month', paid_on) or strftime('%Y-%m', paid_on) does the same job. The principle is to group by the bucket expression itself, not by the full date.

Filling gaps

A month with no payments is missing from the previous result. For a chart, gaps are usually wrong: the line should drop to zero.

There are two honest approaches:

  • Generate the calendar in SQL with a recursive CTE and LEFT JOIN the data onto it.
  • Fill the gaps in the application, where the full list of months is already known.

Filling in the application is simpler and usually right. The key is to state which you did, because "no data" and "not queried" look identical on a chart.

Totals and detail in one result

SQL
Editable. Runs in your browser.





Press Run to see the output.

UNION ALL stacks the detail and the total. It keeps duplicates, which matters here: the total row must survive. A plain UNION would deduplicate and, occasionally, remove a real row.

ORDER BY at the end applies to the combined result, not to each part.

Reconciling a report

A report is trustworthy when its parts add up. Two checks catch most mistakes:

  1. Sum the detail rows and compare with the grand total query.
  2. Run the same filters with COUNT(*) and confirm the row count matches the sum of group counts.
SQL
Editable. Runs in your browser.





Press Run to see the output.

Both numbers should be equal. When they differ, a WHERE is in the wrong place or a join is multiplying rows.

Naming the columns

A report column name is part of the report. sum and count are meaningless three weeks later; captured_rupees and failed_attempts explain themselves in a CSV, a chart legend and a review comment. Include the unit in the name when it is not obvious, and avoid abbreviations that only the author understands. Spend the extra minute, because the name outlives the query.

Consistency matters as much as clarity: if one query calls the field revenue and another calls it amount, reconciling two reports becomes a guessing game. Good names also prevent a real bug, because a column called captured_rupees makes it obvious when someone has summed paise into it.

Writing SUM(amount) / COUNT(*) for an average. That is the average over all rows including failures and zeros, which is rarely "average payment". Use AVG(amount) over the filtered set, and say which set in the label.

Produce a per-month table of attempts and captured revenue in rupees, with a success percentage, for months with at least two attempts.

Show solution
SQL
Editable. Runs in your browser.





Press Run to see the output.

January has two attempts and both captured, so 100 percent. February has three attempts with two captured. March has two attempts, both failed, so zero percent but a real row — which is exactly why the percentage needs the decimal divisor and the report needs the row.

Recap

  • Conditional aggregation counts subsets in one pass and keeps totals consistent.
  • Use 100.0 and NULLIF so percentages never truncate or divide by zero.
  • Group by a bucket expression, and be explicit about gaps and totals.

Module 3 moves from reading to joining tables and changing rows.

Should totals come from SQL or the application?
Either, as long as one source is authoritative. Computing totals twice in two ways is how reports disagree.
Is UNION ALL always faster than UNION?
It avoids the deduplication step, so usually yes, and it is the correct choice when duplicates are meaningful.

Progress is saved in this browser only. No account, no email.

Admin

About the author

Admin

Instructor and technical writer at tutorials.in

Writes beginner lessons for tutorials.in. Each lesson is checked against the language or tool documentation, and examples are run before publishing.

Reviewed by the editorial team before publishing. Last updated November 16, 2026.