Introduction

GROUP BY and NULL cause confusion because SQL engines usually place all NULL values into one aggregate bucket. That behavior is standard, but it is not always what a reporting requirement means by "grouped categories." If the rule is "do not group null," the usual fix is to exclude those rows before aggregation rather than hoping GROUP BY will ignore them automatically.

Why NULL Still Produces a Group

In SQL, NULL means unknown, not an actual value. Even though NULL = NULL is not true in normal comparison logic, grouping still collects all unknown keys into a single group for aggregation.

SELECT sales_region, COUNT(*) AS total
FROM orders
GROUP BY sales_region;

If some rows have no sales_region, the result will include one row where sales_region is NULL. That is often useful for raw data profiling, but it is not always appropriate for business summaries.

Exclude NULL Rows Before Grouping

If the requirement really means "only aggregate known categories," filter the rows first.

1SELECT sales_region, COUNT(*) AS total
2FROM orders
3WHERE sales_region IS NOT NULL
4GROUP BY sales_region
5ORDER BY sales_region;

This is the clearest and most reviewable solution. It states exactly which records participate in the grouped result and avoids hiding the rule inside a more complicated expression.

It is also easier to optimize because the database can reason cleanly about the filter and the grouping key.

Keep Data-Quality Counting Separate

Excluding NULL from the report does not mean ignoring the data-quality issue. If missing values matter operationally, count them in a separate query instead of mixing that concern into the business aggregation.

SELECT COUNT(*) AS missing_sales_region
FROM orders
WHERE sales_region IS NULL;

This separation is useful because it lets one query answer the business question and another answer the pipeline-health question. That is cleaner than trying to overload a single result set to do both jobs.

Use COALESCE Only When You Want a Named Unknown Bucket

Sometimes stakeholders do want missing values visible, but under a readable label such as Unknown. In that case, COALESCE is appropriate.

1SELECT COALESCE(sales_region, 'Unknown') AS region_label,
2       COUNT(*) AS total
3FROM orders
4GROUP BY COALESCE(sales_region, 'Unknown')
5ORDER BY region_label;

This does not "avoid grouping null." It deliberately creates a synthetic unknown category. That is a different business rule and should be documented as such.

Multi-Column Grouping Needs Multi-Column Filtering

With multiple grouping keys, the same principle applies to each key independently. If a row should be excluded whenever any grouping key is missing, filter all of them.

1SELECT country, city, COUNT(*) AS total
2FROM customers
3WHERE country IS NOT NULL
4  AND city IS NOT NULL
5GROUP BY country, city;

Filtering only one column still allows partial unknown groups through the other column. That is a common mistake in reporting code that evolves gradually from one-key grouping to two-key grouping.

Keep Analytics Tools Aligned

Many teams prototype in pandas and then implement in SQL. If the SQL report excludes NULL but the notebook keeps missing values as a separate bucket, you end up with conflicting numbers and wasted debugging time.

The missing-value rule should be stated once and applied consistently across tools:

• exclude before grouping
• keep and label explicitly
• or count separately as a quality metric

Consistency matters more than the specific choice.

Common Pitfalls

The biggest mistake is assuming GROUP BY naturally ignores NULL values. Another is using COALESCE when the real requirement was exclusion, not relabeling. Teams also forget to update the filtering rule when groupings expand to multiple columns, or they remove NULL rows from reports without tracking the missing-data count anywhere else.

Summary

• 'GROUP BY usually creates one NULL bucket by default.'
• If unknown keys should not be grouped, filter them out first with WHERE ... IS NOT NULL.
• Use COALESCE only when you intentionally want an explicit unknown category.
• Apply the same missing-value rule consistently across SQL and analytics tooling.
• Keep business reporting and data-quality monitoring as separate query concerns.