Oracle SQL: Aggregates: WHERE vs HAVING, what COUNT and AVG skip, and comparing to a grand total

Aggregates: WHERE vs HAVING, what COUNT and AVG skip, and comparing to a grand total

SALES holds 9,000 rows: SALE_ID, REGION, AMOUNT NUMBER (null for 1,200 unposted rows), DISCOUNT NUMBER (null for 6,000 rows, where null means 'no discount given'). A developer writes:

SELECT region, COUNT(amount), SUM(amount), AVG(discount) FROM sales WHERE COUNT(*) > 100 GROUP BY region HAVING amount > 0;

It fails. Once it is made to run, the manager objects that the reported average discount of 0.10 is far too high ('most of our sales carry no discount at all') and that the per-region counts do not reconcile to the 9,000-row total.

1. Diagnose the two errors and rewrite the query so both the region filter and the amount filter are legal.

Read the worked answer

Two separate rules are broken.

WHERE COUNT(*) > 100 raises ORA-00934 (group function not allowed here). WHERE is applied per row before grouping, so it cannot see an aggregate. Row-count filters belong in HAVING.
HAVING amount > 0 raises ORA-00979 (not a GROUP BY expression). amount is neither in the GROUP BY nor wrapped in a group function.

SELECT region,
       COUNT(*)                 AS rows_all,
       COUNT(amount)            AS rows_posted,
       SUM(amount)              AS total_amt,
       AVG(NVL(discount,0))     AS avg_discount
FROM   sales
GROUP  BY region
HAVING COUNT(*) > 100
   AND SUM(amount) > 0;

HAVING is evaluated after grouping, so it is the only place an aggregate condition can appear; a HAVING clause without GROUP BY is legal but then the whole table is a single group.

2. Reconcile the numbers: why do the counts not sum to 9,000, and why is the reported average discount 0.10 instead of roughly 0.03?

Read the worked answer

COUNT(*) counts rows; COUNT(amount) counts non-null amounts and therefore skips the 1,200 unposted rows - the two will differ by exactly 1,200 across all regions. Which one the report needs is a business decision, not a syntax one.

AVG(discount) divides SUM(discount) by COUNT(discount), i.e. by the 3,000 rows where a discount exists. If the discount totals 300, the reported figure is 300/3000 = 0.10. Once the 6,000 'no discount' rows are treated as zero, the true average is 300/9000 = 0.033. That is exactly what AVG(NVL(discount,0)) computes - null is not the same as zero to an aggregate function.

The same trap applies to SUM: SUM over an empty set returns null, not 0, so regions with no qualifying rows show a blank total unless wrapped in NVL.

3. The manager now wants every region whose average amount exceeds the company-wide average amount. Show a valid query and explain why the obvious formulation is rejected.

Read the worked answer

You cannot nest group functions: HAVING AVG(amount) > AVG(AVG(amount)) raises ORA-00935 (group function is nested too deeply). The grand total is a different aggregation level, so it must come from a separate query:

SELECT region, AVG(amount) AS region_avg
FROM   sales
GROUP  BY region
HAVING AVG(amount) > (SELECT AVG(amount) FROM sales);

The scalar subquery is evaluated once, so this is efficient. An inline view that computes the overall average and cross-joins it to the grouped result is the equivalent, and is what you use when the comparison value itself depends on several columns.

Note that the comparison uses AVG(amount) - the 1,200 nulls are excluded from both numerator and denominator on both sides, so the comparison stays consistent.