Aggregate NULLs

COUNT Column Skips NULL

COUNT column counts only present cells.

highlighted = computed this step

COUNT column

COUNT of a column counts only present cells in that column.

COUNT column skips NULL\text{COUNT column skips NULL}

Compare counts

COUNT star returns 6, but COUNT of quantity returns 2.

present qty=2\text{present qty}=2

Aggregate NULL examples are tiny finite table transforms; SQL dialect completeness, optimizer behavior, indexing, product behavior, and performance claims are out of scope.

COUNT column skips NULL: base rowsidregionqtynote1east2ok2eastNULLmissing3west5ok4westNULLNULL5northNULLmissing6northNULLNULL Group bucketskeysourceRowsrowCount[all_rows][0, 1, 2, 3, 4, 5]6 Aggregate resultskeysourceRowsaggregates[all_rows][0, 1, 2, 3, 4, 5]{rows_seen:6, qty_seen:2} NULL-skip detailskeyaggregateseencounted/addednullSkippedresult[all_rows]rows_seen6606[all_rows]qty_seen6242 Output rowsrows_seenqty_seen62 Aggregate NULL factsfactvalueinputRowCount6groupCount1aggregateCount2outputRowCount1countStarCountsEveryRowyescountColumnSkipsNullyessumSkipsNullyesallNullSumGivesNULLgroupOrderfirst_seen_input_orderoutputOrderdeterministic_group_order

Same rows, different question

The row count and present-cell count answer different questions.

row count vs cell count\text{row count vs cell count}

Summary

COUNT column skips NULL cells in that column.

NULL cells skipped\text{NULL cells skipped}