COUNT with GROUP BY in SQL to Count Records by Category

COUNT is the simplest and most frequently used aggregate function, and pairing it with GROUP BY answers one of the most common business questions in existence: 'how many of these do we have, broken down by category?' How many orders per customer, how many students per course, how many tickets per support category — all of these are COUNT with GROUP BY questions.

This lesson focuses specifically on COUNT's different forms — COUNT(*), COUNT(column_name), and COUNT(DISTINCT column_name) — since each behaves subtly differently, and choosing the wrong one is a common source of incorrect results in real reporting.

Key Definitions

• COUNT(*): An aggregate function that counts every row within a group, regardless of NULL values in any column.
• COUNT(column_name): An aggregate function that counts only the rows where the specified column has a non-NULL value.
• COUNT(DISTINCT column_name): An aggregate function that counts the number of unique, non-NULL values in the specified column within each group.
• Category: A grouping value used to bucket rows, such as department, city, or product type, against which COUNT is calculated.

What You'll Learn

• Use COUNT(*) combined with GROUP BY to count rows per category.
• Understand the difference between COUNT(*) and COUNT(column_name).
• Apply COUNT(DISTINCT column_name) to count unique values within each group.
• Recognize how COUNT handles NULL values differently depending on which form is used.
• Write practical reports answering 'how many X per Y' business questions.

Detailed Explanation

COUNT(*) counts every single row within each group, without looking at any specific column's value, meaning it includes rows even if some of their columns contain NULL. This makes COUNT(*) the correct choice whenever the question is simply 'how many records exist in this category,' regardless of whether individual fields might be incomplete.

COUNT(column_name), by contrast, only counts rows where that specific column has a non-NULL value. If you write COUNT(phone_number) grouped by city, and some customer rows have a NULL phone_number, those rows will not be included in the count for their city, even though COUNT(*) would have included them. This distinction matters enormously in real data, where incomplete fields are common, and confusing COUNT(*) with COUNT(specific_column) is a frequent source of subtly wrong reports.

COUNT(DISTINCT column_name) adds a further refinement: it counts only the unique, non-NULL values in that column per group, ignoring duplicates. This is essential for questions like 'how many unique customers placed orders in each city,' where simply counting order rows would overcount any customer who placed multiple orders. Using COUNT(DISTINCT customer_id) instead ensures each customer is counted only once per city, regardless of how many separate orders they placed.

Choosing correctly between these three forms requires clearly identifying exactly what the business question is asking: 'how many total records' calls for COUNT(*), 'how many records with this field filled in' calls for COUNT(column_name), and 'how many unique values of this field' calls for COUNT(DISTINCT column_name).

Visual Summary

Draw a small orders table with 5 rows for 'Mumbai' city, where 2 rows share the same customer_id (repeat customer) and one row has a NULL discount_code. Show three outputs side by side: 'COUNT(*) = 5' (all rows), 'COUNT(discount_code) = 4' (excludes the NULL row), and 'COUNT(DISTINCT customer_id) = 4' (counts unique customers, collapsing the repeat).

Quick Reference

Form	What It Counts	Handles NULL How	Handles Duplicates How
COUNT(*)	Every row in the group	Includes rows with NULLs in any column	Counts duplicates normally
COUNT(column_name)	Rows where column_name is not NULL	Excludes NULL values in that column	Counts duplicates normally
COUNT(DISTINCT column_name)	Unique non-NULL values in that column	Excludes NULL values	Counts each unique value only once

SQL Example

CREATE TABLE orders (
  order_id       INT PRIMARY KEY AUTO_INCREMENT,
  customer_id    INT,
  city           VARCHAR(100),
  discount_code  VARCHAR(20)
);

INSERT INTO orders (customer_id, city, discount_code) VALUES
  (101, 'Mumbai', 'SAVE10'),
  (101, 'Mumbai', NULL),
  (102, 'Mumbai', 'SAVE20'),
  (103, 'Mumbai', 'SAVE10'),
  (104, 'Delhi', 'SAVE10');

-- Total orders per city
SELECT city, COUNT(*) AS total_orders
FROM orders
GROUP BY city;

-- Orders with a discount code applied, per city
SELECT city, COUNT(discount_code) AS orders_with_discount
FROM orders
GROUP BY city;

-- Unique customers per city
SELECT city, COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
GROUP BY city;

COUNT(*) grouped by city returns 4 for Mumbai (all four order rows) and 1 for Delhi. COUNT(discount_code) returns 3 for Mumbai, since one of the four Mumbai orders has a NULL discount_code and is excluded. COUNT(DISTINCT customer_id) returns 3 for Mumbai, since customer 101 placed two separate orders but is counted only once as a unique customer.

Real-World Examples

• E-commerce platforms use COUNT(*) grouped by product_category to report total number of orders placed in each category.
• Customer support systems use COUNT(resolution_notes) grouped by agent to measure how many tickets each agent has actually documented, excluding incomplete tickets.
• Marketing teams use COUNT(DISTINCT customer_id) grouped by campaign to measure unique customer reach, rather than counting repeated exposures to the same customer.
• University systems use COUNT(*) grouped by course_id to report total enrollments per course each semester.
• Delivery platforms use COUNT(DISTINCT delivery_partner_id) grouped by city to measure how many unique delivery partners are active in each region.

Common Mistakes to Avoid

• Using COUNT(*) when the question actually requires counting unique values, leading to inflated counts from duplicate entries.
• Using COUNT(column_name) when COUNT(*) was intended, unknowingly excluding rows with NULL values in that column.
• Forgetting that COUNT(DISTINCT column_name) ignores NULL values entirely when calculating uniqueness.
• Not double-checking which specific COUNT form a business question actually requires before writing the query.

Interview Questions

Q1. What is the difference between COUNT(*) and COUNT(column_name)?

COUNT(*) counts every row in a group regardless of NULL values, while COUNT(column_name) only counts rows where that specific column has a non-NULL value, excluding rows where it is NULL.

Q2. When would you use COUNT(DISTINCT column_name) instead of a plain COUNT?

When you need to count unique values rather than total rows, such as counting unique customers who placed orders rather than counting every individual order, which could include multiple orders from the same customer.

Q3. If a city has 10 order rows but only 7 have a non-NULL discount_code, what would COUNT(discount_code) return for that city?

It would return 7, since COUNT(column_name) only counts rows where that specific column is not NULL, excluding the 3 rows with a NULL discount_code.

Q4. How would you count the number of unique products ordered per customer?

Group the orders (or order_items) table by customer_id, and use COUNT(DISTINCT product_id) to count only the unique products each customer ordered, rather than counting every individual order line.

Practice MCQs

1. COUNT(*) counts:

1. Only rows with no NULL values anywhere
2. Every row within a group, regardless of NULLs
3. Only unique rows
4. Only rows with a specific column filled in

Answer: B. Every row within a group, regardless of NULLs

Explanation: COUNT(*) counts all rows in a group without checking any specific column's value, so NULLs in any column do not exclude a row.

2. COUNT(column_name) differs from COUNT(*) because it:

1. Counts faster
2. Excludes rows where that specific column is NULL
3. Always returns a smaller number than COUNT(*)
4. Counts unique values automatically

Answer: B. Excludes rows where that specific column is NULL

Explanation: COUNT(column_name) only counts rows where the named column has a non-NULL value, unlike COUNT(*) which counts all rows regardless.

3. To count unique customers who placed at least one order per city, you should use:

1. COUNT(*)
2. COUNT(order_id)
3. COUNT(DISTINCT customer_id)
4. SUM(customer_id)

Answer: C. COUNT(DISTINCT customer_id)

Explanation: COUNT(DISTINCT customer_id) ensures each customer is counted only once per city, even if they placed multiple orders.

4. If every row in a group has a NULL value in 'discount_code', what does COUNT(discount_code) return for that group?

1. The same as COUNT(*)
2. 0
3. NULL
4. An error

Answer: B. 0

Explanation: Since COUNT(discount_code) excludes NULL values, and every row in the group has a NULL discount_code, the count for that group is 0.

Quick Revision Points

• COUNT(*) counts all rows in a group; COUNT(column_name) excludes rows where that column is NULL.
• COUNT(DISTINCT column_name) counts only unique, non-NULL values within each group.
• Choosing the correct COUNT form depends precisely on what the business question is asking for.
• This distinction is one of the most commonly tested nuances of the COUNT function in interviews and exams.

Conclusion

• COUNT(*), COUNT(column_name), and COUNT(DISTINCT column_name) all answer subtly different questions.
• NULL handling is the key differentiator between COUNT(*) and COUNT(column_name).
• COUNT(DISTINCT column_name) is essential whenever uniqueness, not total row count, is the real question.
• Precisely matching the COUNT form to the business question prevents subtly incorrect reports.

COUNT combined with GROUP BY answers 'how many' questions broken down by category, but its three forms behave differently: COUNT(*) counts every row regardless of NULLs, COUNT(column_name) excludes rows where that column is NULL, and COUNT(DISTINCT column_name) counts only unique non-NULL values. Choosing the correct form based on exactly what a business question requires — total records, records with a field filled in, or unique values — is essential for producing accurate, trustworthy reports.