Oracle Database / Basic: relational and transaction foundations / Lesson 5

Grouping and trustworthy totals

Calculate freight summaries with explicit units, missing-value rules and subtotal labels.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Distinguish group filters from row filters.
Build conditional counts and identify generated subtotal rows.

Define the denominator before the function

An average charge per shipment differs from an average invoice-line amount, even if both use AVG. Define the input grain and denominator before choosing the aggregate. COUNT(*) counts rows, COUNT(charge) counts non-NULL charge values, and SUM ignores NULL inputs rather than treating every absence as zero. On an empty input without grouping, COUNT returns zero while SUM returns NULL. Replacing that NULL with zero is a business presentation decision; it must not conceal that the data set was empty or an upstream feed failed to arrive.

Count categories without losing the denominator

Conditional aggregation keeps the population visible while counting a category within it. In the fixture, there are three shipments, two delivered records and a known-charge total of fifteen. The missing charge does not contribute to SUM. COUNT(CASE WHEN ... THEN 1 END) relies on the omitted ELSE becoming NULL. SUM(CASE WHEN ... THEN 1 ELSE 0 END) gives the same category count for a nonempty group, but its empty-input result still follows SUM semantics. Choose one idiom and explain it rather than allowing NULL behavior to remain accidental.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

WITH shipments AS (
 SELECT 'delivered' status, 10 charge FROM dual UNION ALL
 SELECT 'held', 5 FROM dual UNION ALL
 SELECT 'delivered', NULL FROM dual
)
SELECT COUNT(*) AS shipments,
       COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered,
       SUM(charge) AS known_charge
FROM shipments;

Expected output

SHIPMENTS | DELIVERED | KNOWN_CHARGE
3         | 2         | 15

All three rows enter the population; two satisfy the category and only two have known charge values.

WHERE and HAVING answer different questions

WHERE removes source rows before grouping. HAVING removes groups after their aggregates have been computed. To list customers whose October shipment charges exceed one hundred, filter October rows in WHERE and use HAVING SUM(charge) > 100. Filtering individual charges above one hundred first asks a different question and discards smaller qualifying shipments from the total. Every selected expression must be grouped, aggregated or otherwise valid under Oracle grouping rules; do not import another engine’s permissive choice of an arbitrary ungrouped column. A syntax error here protects against an ambiguous report.

Distinguish missing dimension values from subtotals

ROLLUP and GROUPING SETS create multiple aggregation levels in one result. A NULL customer identifier might be a real missing source value or a generated grand-total placeholder. GROUPING(customer_id) distinguishes them: one means the column was rolled up, zero means it belongs to the grouping level. Do not label every NULL as TOTAL. Keep a grouping marker in the data contract when a downstream spreadsheet or API consumes subtotals, because a visual label alone can collide with a real customer name. Reconcile each level independently against the same filtered population.

sql Illustrative technical example; logical results are reasoned, not engine-tested.

SELECT customer_id, GROUPING(customer_id) AS is_total, SUM(charge) AS total_charge
FROM shipments
GROUP BY GROUPING SETS ((customer_id), ());

One group per customer and one overall group are returned. is_total distinguishes the overall row even if a source customer_id is NULL.

Round at the documented accounting boundary

SUM(ROUND(charge,2)) and ROUND(SUM(charge),2) can disagree when source amounts contain more than two decimal places. The first rounds each shipment; the second rounds the aggregate. Neither is universally correct without an accounting rule. Currency conversion also needs a rate date, currency identity and documented rounding direction. Keep exact NUMBER arithmetic for bounded decimal amounts and avoid converting them to formatted strings before summation. Validate the report with adversarial small values near half-cent boundaries, plus an empty period and a customer whose entire charge population is missing.

Common errors and fixes

ORA-00979

Why it happens: A selected expression is neither grouped nor aggregated appropriately.
How to fix it: Choose the intended group key or an aggregate with a justified meaning.
Prevent it next time: State the output grain before adding descriptive columns.

Delivered percentage is too high

Why it happens: WHERE removed nondelivered rows before the denominator was counted.
How to fix it: Keep the full target population and use conditional aggregation.
Prevent it next time: Review numerator and denominator as separate business definitions.

A missing customer is labelled as the grand total

Why it happens: NULL was used as a subtotal marker without GROUPING.
How to fix it: Include the grouping indicator and label only generated subtotal rows.
Prevent it next time: Test real nullable dimensions together with grouping sets.

Check your understanding

1. Charges 40, 40 and 30 belong to one customer. Which filter finds customers totaling more than 100?

Show worked solution

Group the three shipment rows by customer and apply HAVING SUM(charge) > 100. The customer qualifies with 110. WHERE charge > 100 would discard all three source rows. Apply legitimate period and access filters before grouping, while keeping the aggregate threshold after it.

2. What should an API preserve when it returns both customer totals and a grand total?