Oracle 1Z0-084: The Regional Dashboard That Adds Up to More Than 100%

The Regional Dashboard That Adds Up to More Than 100%

OCEANIC_RETAIL publishes a weekly regional dashboard from ORDERS (ORDER_ID NUMBER primary key, REGION_ID NUMBER NOT NULL, STATUS VARCHAR2(12) NOT NULL, ORDER_TOTAL NUMBER, DISCOUNT_PCT NUMBER). The table holds 48,210 rows: 3,140 have STATUS='CANCELLED' and a null ORDER_TOTAL; 9,870 have a null DISCOUNT_PCT. The analyst's query is:

SELECT region_id, COUNT(*) AS orders, SUM(order_total) AS revenue, AVG(order_total) AS aov, COUNT(discount_pct) AS discounted FROM orders GROUP BY region_id HAVING SUM(order_total) > 100000;

Finance reports three symptoms. For region 40, orders multiplied by aov gives 1,000,000 but revenue shows 920,000. Region 12, which booked only 61,000, is missing from the dashboard entirely. And the 'discounted' column is being read as a percentage of all orders. Management wants cancelled orders shown as their own counter, no region dropped for being small, and a visible 0 rather than a blank where no discounts exist.

1. Explain arithmetically why ORDERS multiplied by AOV does not equal REVENUE for region 40, and why COUNT(discount_pct) is not a share of all orders.

Read the worked answer

Every aggregate except COUNT(*) ignores NULLs, and each one ignores them independently.

COUNT(*) counts rows: region 40's 5,000 rows include 400 cancelled orders whose ORDER_TOTAL is null.
SUM(order_total) and AVG(order_total) skip those 400 rows, so AVG divides by 4,600, not 5,000.
With SUM = 920,000 the average is 920,000 / 4,600 = 200. Multiplying the average back by the row count gives 200 x 5,000 = 1,000,000. The 80,000 discrepancy is exactly the 400 null rows being counted by COUNT(*) but excluded from the average.

The rule: AVG(x) = SUM(x) / COUNT(x), never SUM(x) / COUNT(*). If you publish both COUNT(*) and AVG(x) in one row of a report, readers will assume the denominator is COUNT(*); either make the denominator explicit or label the column "average of non-null values".

COUNT(discount_pct) counts non-null discounts, and it counts them across all rows including cancelled ones. So its value is neither "live orders that received a discount" nor a percentage. Dividing it by COUNT(*) silently mixes a non-null-value count with a row count over a different population - the same mistake as above, one column over.

2. Rewrite the query so that cancelled orders appear as a separate counter, no region is dropped for being small, and zero shows as 0. Show the SQL and justify each change.

Read the worked answer

SELECT region_id,
       COUNT(*)                                                  AS rows_total,
       COUNT(CASE WHEN status <> 'CANCELLED' THEN 1 END)          AS live_orders,
       COUNT(CASE WHEN status =  'CANCELLED' THEN 1 END)          AS cancelled_orders,
       NVL(SUM(CASE WHEN status <> 'CANCELLED' THEN order_total END), 0) AS revenue,
       ROUND( SUM(CASE WHEN status <> 'CANCELLED' THEN order_total END)
              / NULLIF(COUNT(CASE WHEN status <> 'CANCELLED' THEN 1 END), 0), 2) AS aov,
       COUNT(discount_pct)                                        AS discounted_orders
FROM   orders
GROUP  BY region_id
ORDER  BY region_id;

Why each change:

Conditional aggregation instead of a WHERE on status. Filtering cancelled rows in WHERE would make them uncountable; CASE inside the aggregate keeps them visible in their own column while excluding them from revenue.
HAVING removed. The threshold was deleting region 12. If the business still wants a flag, add CASE WHEN SUM(...) > 100000 THEN 'Y' ELSE 'N' END AS flagged - a projection, not a filter.
Explicit AOV denominator. SUM/COUNT over the same CASE predicate guarantees that aov * live_orders reconciles with revenue. NULLIF(...,0) prevents a division-by-zero on a region whose orders are all cancelled.
COUNT never returns NULL. For any region that has rows, COUNT(discount_pct) already returns 0, not blank. A blank therefore comes from somewhere else - typically an outer join to a REGIONS table where you wrote COUNT(*) (which returns 1 for a region with no matching order) or a reporting-layer NVL2. If regions come from a separate table, drive from it with a LEFT JOIN and count e.order_id, not *.

3. A colleague proposes moving the revenue threshold into a WHERE clause so the query 'runs faster'. What is wrong with that, and what does the placement of WHERE versus HAVING actually control here?

Read the worked answer

WHERE SUM(order_total) > 100000 raises ORA-00934: group function is not allowed here. WHERE is evaluated per row before any group exists, so a group function has nothing to operate on. Only HAVING sees groups.

The two clauses also control different populations, which matters more than speed:

WHERE removes rows before grouping. WHERE status <> 'CANCELLED' is legitimate and desirable - it reduces the aggregation workload - but it also makes the cancelled counters impossible in the same query, because those rows no longer exist. You would need a second query and a UNION ALL.
HAVING removes groups after aggregation. Putting the revenue test there is the only syntactically valid option, and it changes the result set by deleting entire regions - the exact defect management complained about.

On performance: HAVING does not prevent a full scan; it only discards groups after they are built. The real savings come from WHERE on base columns (status, region, dates) that can use an index on (status, region_id) or allow partition pruning. So the correct split is: row-level predicates in WHERE when you do not need the filtered rows for anything else, group-level predicates in HAVING, and CASE inside aggregates when one scan must feed several populations.