COUNT in SQL — The Complete Guide for Data Analysts

SELECT COUNT(*) AS total_orders
FROM Orders;
This query returns the total number of rows in the Orders table.
Business meaning:
“How many orders exist in total?”
COUNT(column_name)
COUNT(column_name) counts only non-NULL values in that column.
That is a very important distinction.
Example:
SELECT COUNT(customer_id) AS customers_with_id
FROM Orders;
This counts only rows where customer_id is not NULL.
If some rows have NULL values in that column, they will not be included.
That makes COUNT(column_name) useful when you want to count valid values instead of total rows.
COUNT(DISTINCT column_name)
COUNT(DISTINCT column_name) counts unique non-NULL values.
Use it when you want to know how many different entities exist.
Example:
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM Orders;
This tells you how many unique customers placed orders.
That is often more useful than counting total rows, because one customer may place many orders.
The difference between the three forms
This is one of the most important things to understand in this article.
Form
What it counts
Best use
COUNT(*)
All rows
Total records
COUNT(column)
Non-NULL values in one column
Valid entries
COUNT(DISTINCT column)
Unique values
Unique customers, products, cities, etc.
If you confuse these three, your numbers can become misleading.
Business interpretation
Suppose the Orders table contains 10,000 rows.
If you run COUNT(*), the answer is 10,000.
If you run COUNT(customer_id), the answer may be smaller if some rows have NULL customer IDs.
If you run COUNT(DISTINCT customer_id), the answer may be much smaller because the same customer can place multiple orders.
That is why analysts must choose the right version based on the business question.
When analysts use COUNT
COUNT appears everywhere in analytics.
Examples:
How many users signed up?
How many orders were completed?
How many customers returned this month?
How many products were sold?
How many support tickets were resolved?
How many employees belong to each department?
Once you learn to see business questions in terms of volume, you will notice COUNT everywhere.
COUNT with GROUP BY
This is where COUNT becomes much more useful.
Without grouping, COUNT gives one total.
With GROUP BY, it gives one total per category.
Examples:
SELECT city, COUNT(*) AS total_customers
FROM Customers
GROUP BY city;
This answers:
“How many customers are in each city?”
Another example:
SELECT category_id, COUNT(*) AS total_products
FROM Products
GROUP BY category_id;
This answers:
“How many products exist in each category?”
That is the point where basic counting becomes real business analysis.
Visual framework: how COUNT works
Raw rows
↓
COUNT
↓
One number
↓
Business question answered
And when grouping is involved:
Raw rows
↓
GROUP BY category
↓
COUNT within each group
↓
Summary by segment
This is the analytical pattern behind most reporting queries.
COUNT and HAVING
When you want to filter groups based on count, you use HAVING.
Example:
SELECT city, COUNT(*) AS total_customers
FROM Customers
GROUP BY city
HAVING COUNT(*) > 100;
This returns only cities with more than 100 customers.
That is a common business pattern:
cities with enough customers,
products with enough orders,
campaigns with enough conversions,
departments with enough employees.
HAVING works after aggregation. WHERE works before aggregation.
That distinction matters a lot.
Practical business examples
1. Count total orders
SELECT COUNT(*) AS total_orders
FROM Orders;
Business question:“How many orders do we have in total?”
2. Count unique customers who placed orders
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM Orders;
Business question:“How many different customers purchased something?”
3. Count orders by city
SELECT customer_city, COUNT(*) AS order_count
FROM Orders
GROUP BY customer_city;
Business question:“How many orders came from each city?”
4. Count products in each category
SELECT category_id, COUNT(*) AS product_count
FROM Products
GROUP BY category_id;
Business question:“How many products are available in each category?”
5. Count employees by department
SELECT department_id, COUNT(*) AS employee_count
FROM Employees
GROUP BY department_id;
Business question:“How many employees work in each department?”
COUNT and NULL values
A very important detail:
COUNT(*) counts rows.
COUNT(column) ignores NULLs.
That means NULL values can change the result.
Example:
SELECT COUNT(email) AS valid_emails
FROM Customers;
If some customers do not have an email address recorded, those rows are excluded.
That makes COUNT(column) useful for measuring data completeness.
For example:
valid emails,
filled phone numbers,
available shipping dates,
recorded payment values.
COUNT for data quality checks
Analysts do not only use COUNT for reporting.
They also use it for validation.
Examples:
How many rows are missing a customer ID?
How many orders have no payment record?
How many products are inactive?
How many records entered a table today?
This is one reason COUNT is so valuable. It supports both analysis and data quality checking.
Common misconceptions about COUNT
COUNT always means total rows
Not always.
It depends on the syntax.
COUNT(*) = all rows
COUNT(column) = non-NULL values
COUNT(DISTINCT column) = unique values
COUNT(column) is the same as COUNT(*)
Not true.
If the column contains NULL values, the results differ.
COUNT(DISTINCT ...) is only for advanced SQL
No. It is one of the most practical counting tools in analytics.
Counting rows always gives the answer you need
Not necessarily.
Sometimes the real question is unique customers, not orders.
Sometimes it is valid records, not total rows.
The business question should decide the function.
Comparing COUNT with SUM, AVG, MIN, and MAX
COUNT is different from the other aggregate functions because it measures volume, not value.
COUNT → how many
SUM → how much in total
AVG → what is the average
MIN → what is the smallest
MAX → what is the largest
If the question is about size, use COUNT.
If the question is about value, use the appropriate numeric aggregate.
Real analyst workflow
Imagine the Head of Marketing says:
“I want to know how many customers came from each campaign.”
As an analyst, your thought process is:
Identify the metric.
Realize the metric is a volume metric.
Choose COUNT.
Decide whether the result should be grouped by campaign.
Write the query.
Check whether NULL values or duplicates affect the result.
Present the business insight clearly.
That is a real analytical workflow, not just a SQL exercise.
Analyst Tip
When using COUNT, always ask one extra question:
Am I counting rows, valid values, or unique entities?
That one question prevents a surprising number of reporting mistakes. Many bad dashboards are not caused by bad SQL syntax—they are caused by the wrong type of count.
The Grito Factor
A lot of business teams think they are asking for “just a number,” but the choice between COUNT(*), COUNT(column), and COUNT(DISTINCT ...) can completely change the meaning of that number. In analytics, the smallest syntax choice often changes the business story. That is why strong analysts are careful not just with what they count, but with how they count it.
Interview perspective
< u>COUNT is one of the most common interview topics because it tests whether you understand aggregation, NULL handling, grouping, and business interpretation.
Common interview questions include:
What is the difference between COUNT(*) and COUNT(column)?
When would you use COUNT(DISTINCT ...)?
How do you count customers by city?
How do you count only rows that meet a condition?
Why might COUNT return a different result from the number of rows in a table?
A strong answer always explains the business reason first, then the SQL logic.
Common mistakes
1. Using COUNT(*) when unique values are needed
If a customer places 10 orders, COUNT(*) counts 10 rows, not 1 customer.
Correct approach:Use COUNT(DISTINCT customer_id) when you want unique customers.
2. Forgetting NULL handling
COUNT(column) ignores NULLs.
If you expect all rows to be counted, this can create confusion.
3. Using COUNT(DISTINCT ...) when duplicates are meaningful
Sometimes repeated actions matter.
For example, if you are analyzing total orders, duplicate customers are not a mistake—they are part of the business behavior.
4. Counting the wrong entity
If the question is “How many customers bought something?”, counting orders is not enough.
You need the right entity, not just any entity.
5. Not checking the business definition
“Active customers” may mean:
customers with at least one order,
customers who purchased in the last 30 days,
customers with an active subscription.
The SQL can only be correct if the business definition is clear.
Practice questions
Beginner
Which version of COUNT would you use to count all rows in the Orders table?
What does COUNT(column) do differently from COUNT(*)?
Intermediate
How would you count the number of unique customers who placed orders?
How would you count the number of products in each category?
Advanced
Your manager asks for cities with more than 100 customers. Which SQL concepts would you combine?
(Hint: COUNT, GROUP BY, and HAVING.)
What comes next
Now that COUNT is clear, the next step is to learn SUM. Counting tells you how many events occurred. SUM tells you how much value those events represent.
That transition is important because most dashboards do not stop at volume. They move from quantity to value.
Final thoughts
COUNT is one of the simplest SQL functions, but it is also one of the most important.
It helps analysts measure business activity, understand volume, validate data, and build reports that answer real operational questions.
If you can choose the right type of count, you are already thinking like an analyst instead of a syntax memorizer.
Continue your learning
Next lessons in this cluster:
SUM
AVG
MIN
MAX
SQL Grouped Analysis Examples
Related lessons:
GROUP BY
HAVING
SELECT statement
WHERE clause
SQL Aggregate Functions
The next article will show how SUM turns raw numbers into total business value.