WHERE vs HAVING

Aggregates per Group

COUNT and SUM are computed for remaining groups.

highlighted = computed this step

Aggregate each group

COUNT and SUM are computed from the remaining rows inside each bucket.

aggregate remaining rows\text{aggregate remaining rows}

Read aggregate facts

The compiler computes 2 aggregate facts for each of 3 groups.

facts per group=2\text{facts per group}=2

HAVING examples are tiny finite group-filter transforms; SQL dialect completeness, optimizer behavior, indexing, and product claims are out of scope.

Aggregates after WHERE: base rowsidregionstatusqty1eastopen22eastopen33westopen14westclosed45northNULL26northopen1 WHERE before groupingsourceinputwhereTruthgroupedRole0[1, east, open, 2]TRUEgrouped1[2, east, open, 3]TRUEgrouped2[3, west, open, 1]TRUEgrouped3[4, west, closed, 4]FALSEdropped_before_grouping4[5, north, NULL, 2]UNKNOWNdropped_before_grouping5[6, north, open, 1]TRUEgrouped GROUP BY bucketskeysourceRowsrowCount[east][0, 1]2[west][2]1[north][5]1 Aggregate facts per groupkeysourceRowsaggregates[east][0, 1]{n:2, total_qty:5}[west][2]{n:1, total_qty:1}[north][5]{n:1, total_qty:1} HAVING result per groupkeypredicatetruthoutputRole[east]noneTRUEkept[west]noneTRUEkept[north]noneTRUEkept HAVING output groupsregionntotal_qtyeast25west11north11 HAVING factsfactvalueinputRowCount6whereKeptRows4whereDroppedRows2groupCount3aggregateCount2outputGroupCount3havingTrueCount3havingFalseCount0havingUnknownCount0groupOrderfirst_seen_after_wherepipelineWHERE_before_GROUP_BY_then_HAVING

HAVING reads facts

HAVING can read these group facts because they now exist.

group facts now exist\text{group facts now exist}

Summary

In this tiny model, aggregate facts are COUNT rows and integer SUM.

COUNT and SUM\text{COUNT and SUM}