GROUP BY with NULL values behavior in SQL

Introduction

We use GROUP BY to group rows that have the same values in specified columns. Understanding how NULL values behave helps us group data correctly when some values are missing.

You want to count or summarize data by categories that might have missing values.

You need to group sales data by product category, but some products have no category assigned (NULL).

You want to see how many users belong to each department, including those without a department (NULL).

You want to group survey answers where some responses are missing (NULL).

Syntax

SQL

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;

NULL values in the GROUP BY column are treated as a single group.

All rows with NULL in the grouped column are grouped together.

Examples

This groups employees by their department. Employees with NULL department are grouped together.

SQL

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

This sums sales by product category. Products with NULL category are grouped as one group.

SQL

SELECT category, SUM(sales)
FROM products
GROUP BY category;

Sample Program

This example creates an employees table with some NULL departments. It groups employees by department, counting how many are in each group. NULL departments are grouped together.

SQL

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  department VARCHAR(50)
);

INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'HR'),
(2, 'Bob', NULL),
(3, 'Charlie', 'IT'),
(4, 'David', NULL),
(5, 'Eve', 'HR');

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
ORDER BY department;

Important Notes

NULL values are grouped together as one group in GROUP BY.

Ordering by the grouped column may show NULL as empty or at the start/end depending on the database.

Summary

GROUP BY treats all NULL values in a column as one group.

This helps include rows with missing values in grouped results.