Aggregate NULLs

COUNT Star Counts Rows

COUNT star counts every row in a bucket.

highlighted = computed this step

COUNT star

COUNT star looks at rows, not at one nullable column.

COUNT star counts rows\text{COUNT star counts rows}

Read the count

The compiler sees 6 rows and returns 6.

COUNT star=6\text{COUNT star}=6

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

COUNT star counts rows: 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} NULL-skip detailskeyaggregateseencounted/addednullSkippedresult[all_rows]rows_seen6606 Output rowsrows_seen6 Aggregate NULL factsfactvalueinputRowCount6groupCount1aggregateCount1outputRowCount1countStarCountsEveryRowyescountColumnSkipsNullyessumSkipsNullyesallNullSumGivesNULLgroupOrderfirst_seen_input_orderoutputOrderdeterministic_group_order

NULL cells still count

Rows with NULL quantity cells are still rows.

row counted\text{row counted}

Summary

COUNT star counts every source row in the bucket.

every row\text{every row}