Average Discount and Rollup Labels with Null Groups
A retail chain stores first-quarter sales in ORDERS_FACT, which has 12,400 rows. Columns are ORDER_ID, REGION (VARCHAR2(20), nullable), CHANNEL (VARCHAR2(20)), ORDER_TOTAL (NUMBER, NOT NULL), and DISCOUNT_PCT (NUMBER, nullable). Data quality issues are known: 310 rows have REGION NULL because of a load defect, and 1,150 rows have DISCOUNT_PCT NULL because no discount was applied, not because the value is unknown. A finance analyst runs:
SELECT region, COUNT(*) AS orders, COUNT(discount_pct) AS discounted, AVG(discount_pct) AS avg_disc FROM orders_fact GROUP BY region;
The analyst expected AVG(DISCOUNT_PCT) to match the intuition of total discount divided by order count, and complains that some regional averages look too high. The report must exclude CHANNEL = 'TEST' but still show all regions, including the null region. The analyst also wants per-region totals and a grand total in one result set, but an initial ROLLUP attempt produced a null REGION that could not be distinguished from the real null-region group.
1. Why does AVG(DISCOUNT_PCT) look too high, and how should the query compute the average if null discount means a zero discount?
Read the worked answer
AVG ignores nulls, so its denominator is COUNT(discount_pct), not COUNT(*). In a region with 1,000 orders where only 300 rows have a non-null discount, AVG divides the sum by 300. If the 700 nulls truly mean 0 percent, that inflates the result. The analyst is comparing two different populations: average discount among discounted orders versus average discount across all orders.
Correct expressions when null means zero:
AVG(NVL(discount_pct, 0)) AS avg_disc_all_orders
or, equivalently and more explicitly:
SUM(NVL(discount_pct, 0)) / NULLIF(COUNT(*), 0) AS avg_disc_all_orders
Note that SUM alone ignores nulls, which is harmless here because null contributes zero to the numerator; the important fix is the denominator. NULLIF prevents division by zero for an empty group. If null instead meant unknown, keep AVG(discount_pct) and report COUNT(discount_pct) alongside so readers know the basis. Do not replace nulls with zero blindly in both cases; the business meaning decides. Also check COUNT(*) versus COUNT(1): both count rows, while COUNT(discount_pct) counts non-null values.
2. Where should CHANNEL = 'TEST' be filtered, and how should null regions be handled in grouping and display?
Read the worked answer
CHANNEL is a row-level condition, so it belongs in WHERE, which is evaluated before grouping. HAVING is for group-level predicates and is evaluated after grouping, so using it for a plain column filter is misleading and may force unnecessary aggregation. Write:
WHERE (channel <> 'TEST' OR channel IS NULL)
GROUP BY region
The OR IS NULL matters: channel <> 'TEST' evaluates to unknown for a null channel, so those rows would be dropped. If null channels are known to be impossible, the simpler predicate is fine, but verify with a count first.
GROUP BY does not drop null groups. All 310 null-region rows form one group, which is why the report still shows a region row with no label. Display it with NVL(region, 'UNASSIGNED') in the SELECT list, not in the GROUP BY, unless the expression is needed for grouping. Do not write WHERE region = NULL; it returns no rows. If the requirement instead is to exclude the null region, that is a WHERE predicate and should be stated explicitly. Keep the test-channel exclusion and the null-region inclusion as separate decisions.
3. How can the same result set show per-region totals and a grand total without confusing the grand total with the real null-region group?
Read the worked answer
ROLLUP and GROUPING SETS generate a total row in which REGION is null, which collides with a stored null region. Use the GROUPING function to label generated rows:
SELECT CASE WHEN GROUPING(region) = 1 THEN 'ALL REGIONS'
ELSE NVL(region, 'UNASSIGNED')
END AS region_label,
SUM(order_total) AS total,
GROUPING(region) AS is_total
FROM orders_fact
WHERE (channel <> 'TEST' OR channel IS NULL)
GROUP BY ROLLUP(region)
ORDER BY GROUPING(region), region;
GROUPING returns 1 for rows generated by ROLLUP, and 0 for stored data, including the real null-region group. Do not remove the apparent nulls with HAVING region IS NOT NULL; that would also remove the genuine null group and silently drop those 310 rows. If there were multiple dimensions, GROUPING_ID or explicit GROUPING SETS would be clearer than chained ROLLUPs, because the number of null combinations grows. Also verify that the grand total equals the sum of the regional totals after the test-channel filter; if it does not, check for filters applied in the wrong clause.