Group By Aggregates

Group COALESCE Category

COALESCE can label missing group keys before GROUP BY makes buckets.

highlighted = computed this step

Input table

Start with the same sales rows where two category cells are NULL.

Sales with NULL category\text{Sales with NULL category}
Salesorder_idcategoryamounts1book20s2NULL15s3pen5s4NULL10

Label missing categories

COALESCE turns NULL category into Uncategorized before grouping.

COALESCE category label\text{COALESCE category label}
COALESCE category labelorder_idcategoryamountcategory_labels1book20books2NULL15Uncategorizeds3pen5pens4NULL10Uncategorized

Group query

Group by the labeled category and count every row.

GROUP BY category label\text{GROUP BY category label}
GROUP BY labeled categorygroup_byarity 2 rows 3select order_id,category,amount,category_labelarity 4 rows 4Salesarity 3 rows 4

Grouped output

The missing category rows now appear under the Uncategorized label.

labeled category buckets\text{labeled category buckets}
Grouped outputcategory_labelorder_countUncategorized2book1pen1