Part 3: Mastering the COUNT() Function | SQL Challenges & Capstone
SQL Aggregation & Grouping Practice · Part 3 · Lecture Notes
Mastering the COUNT() Function
How to measure operational volume and business activity — counting total records, filtered subsets, and building the count metrics that power dashboards.
Part 3 of 742% Complete
COUNT(*)
COUNT(column)
WHERE Filter
Business Volume
8 Practice Questions
Learning Objectives
Use COUNT(*) to count the total number of rows in a table
Use COUNT(column_name) to count only non-NULL values in a specific column
Combine COUNT(*) with WHERE to count only records that meet a specific condition
Understand that COUNT() always returns a whole number and never requires ROUND()
Click any section header to open or close it
COUNT() counts the number of records inside a table or column. It is the most direct way to measure volume in SQL — how many customers, how many transactions, how many open tickets, how many active subscriptions. Like all aggregate functions, COUNT() returns a single value regardless of table size.
In industry, analysts use COUNT() to measure customer activity levels, calculate operational totals, count transactions by type, track support ticket volume, measure campaign reach, and produce the basic count metrics that appear in every business dashboard.
COUNT(*) vs COUNT(column)
COUNT(*) counts every row in the table — including rows where some columns are NULL. COUNT(column_name) counts only rows where that specific column is not NULL. For measuring total records, COUNT(*) is the standard choice.
Basic COUNT() Syntax
SELECTCOUNT(column_name)
FROM table_name;
— Or to count all rows:
SELECTCOUNT(*)
FROM table_name;
COUNT(*) is the universal record counter. It counts every row in the table unconditionally — it does not care whether specific columns have values or not. For measuring “how many X exist in this table?”, COUNT(*) is always the correct form.
When to Use COUNT(*) vs COUNT(column)
COUNT(*): “how many rows are in this table?” — includes NULLs, counts everything. COUNT(column_name): “how many rows have a value in this column?” — excludes NULLs. For general volume metrics, always use COUNT(*).
Count All Users
SELECTCOUNT(*) AS total_users
FROM users;
Count All Orders
SELECTCOUNT(*) AS total_orders
FROM orders;
Combining COUNT(*) with a WHERE clause counts only the rows that match the filter condition — the number of completed orders, the number of active subscriptions, the number of open tickets. This is the standard pattern for business volume reporting by status, type, or category.
WHERE Filters Before COUNT
The clause order is: FROM identifies the table, WHERE filters the rows, THEN COUNT(*) counts the filtered result. WHERE does not go inside COUNT() — it goes before it in the standard clause sequence.
Count Completed Orders Only
SELECTCOUNT(*) AS completed_orders
FROM orders
WHERE order_status = ‘Completed’;
Count Active Subscriptions Only
SELECTCOUNT(*) AS active_subscriptions
FROM subscriptions
WHERE status = ‘Active’;
COUNT() is the backbone of operational dashboards. Every KPI that reads “Total X” — total customers, total tickets, total campaigns — is a COUNT() query under the hood. Analysts run COUNT() queries at the start of any new analysis to understand the volume of data they are working with.
First Query on Any New Table
SELECT COUNT(*) FROM table_name is standard practice when working with an unfamiliar table. It tells the analyst immediately whether they are working with 8 rows or 8 million — and whether any WHERE filter has meaningfully narrowed the dataset.
Count All Subscriptions
SELECTCOUNT(*) AS total_subscriptions
FROM subscriptions;
Count Open Support Tickets
SELECTCOUNT(*) AS open_tickets
FROM support_tickets
WHERE ticket_status = ‘Open’;
Work through each question below. Run your query first, then use Check Answer. A wrong answer reveals the correct SQL with a Copy button; a correct result earns a congratulations message.
Practice 1Count the total number of users
AI Prompt
Write a MySQL query to count the total number of users.
SQL Answer
SELECTCOUNT(*) AS total_users
FROM users;
Practice 2Count the total number of transactions
AI Prompt
Write a MySQL query to count the total number of transactions.
SQL Answer
SELECTCOUNT(*) AS total_transactions
FROM transactions;
Practice 3Count the total number of products
AI Prompt
Write a MySQL query to count the total number of products.
SQL Answer
SELECTCOUNT(*) AS total_products
FROM products;
Practice 4Count the total number of completed orders
AI Prompt
Write a MySQL query to count the total number of completed orders.
SQL Answer
SELECTCOUNT(*) AS completed_orders
FROM orders
WHERE order_status = ‘Completed’;
Practice 5Count the total number of active subscriptions
AI Prompt
Write a MySQL query to count the total number of active subscriptions.
SQL Answer
SELECTCOUNT(*) AS active_subscriptions
FROM subscriptions
WHERE status = ‘Active’;
Practice 6Count the total number of open support tickets
AI Prompt
Write a MySQL query to count the total number of open support tickets.
SQL Answer
SELECTCOUNT(*) AS open_tickets
FROM support_tickets
WHERE ticket_status = ‘Open’;
Practice 7Count the total number of campaigns using the Email channel
AI Prompt
Write a MySQL query to count the total number of campaigns using the Email channel.
SQL Answer
SELECTCOUNT(*) AS email_campaigns
FROM campaigns
WHERE channel = ‘Email’;
Practice 8Count the total number of Mobile sessions
AI Prompt
Write a MySQL query to count the total number of Mobile sessions.
SQL Answer
SELECTCOUNT(*) AS mobile_sessions
FROM sessions
WHERE device_type = ‘Mobile’;
Knowledge Check
Five questions — select the best answer for each.
1. What does COUNT(*) count?
AOnly rows where all columns have values
BOnly rows where the specified column is not NULL
CEvery row in the table, including rows with NULL values in some columns
DThe number of unique values in the table
Correct. COUNT(*) counts all rows unconditionally. It doesn’t matter whether specific columns have NULL values — every row is counted.
Not quite. COUNT(*) counts all rows regardless of NULL values — every row in the table is included.
2. What is the difference between COUNT(*) and COUNT(column_name)?
AThey always return the same result
BCOUNT(*) counts all rows; COUNT(column_name) skips rows where that column is NULL
CCOUNT(*) is faster; COUNT(column_name) is more accurate
DCOUNT(column_name) counts all rows; COUNT(*) skips NULLs
Correct. COUNT(*) includes every row. COUNT(column_name) only counts rows where that column has a non-NULL value. For total record volume, COUNT(*) is always correct.
Not quite. COUNT(*) counts all rows including NULLs. COUNT(column_name) skips rows where the column is NULL.
3. Which query counts only completed orders?
ASELECT COUNT(order_status = ‘Completed’) FROM orders;
BSELECT COUNT(*) FROM orders WHERE order_status = ‘Completed’;
CSELECT COUNT(*) HAVING order_status = ‘Completed’ FROM orders;
DSELECT COUNT(COMPLETED) FROM orders;
Correct. WHERE filters the rows before COUNT(*) applies. The structure is FROM table → WHERE condition → COUNT(*) counts the filtered result.
Not quite. WHERE filters the rows before counting. The correct structure is SELECT COUNT(*) FROM orders WHERE order_status = ‘Completed’.
4. Does COUNT() require ROUND() like SUM() and AVG()?
AYes — COUNT() always produces decimal values
BNo — COUNT() always returns a whole number (integer)
CYes — only when counting date columns
DIt depends on the size of the table
Correct. COUNT() counts rows — the result is always a whole number. There are no decimal places to round. ROUND() is only needed for functions that calculate decimals like SUM() and AVG().
Not quite. COUNT() always returns an integer (whole number). No ROUND() is needed.
5. Why do analysts run COUNT(*) as one of the first queries on a new table?
ATo delete any duplicate records before analysis
BTo understand the volume of data they are working with
CTo create an index that speeds up future queries
DTo check for syntax errors in the table structure
Correct. Knowing the row count tells analysts whether they are working with a small or large dataset, and whether any subsequent WHERE filter has meaningfully narrowed the results.
Not quite. COUNT(*) tells the analyst how many rows exist — essential context before filtering, aggregating, or reporting on the data.
Part 3 Summary
The key ideas from this lecture — keep these in mind as you move forward.
COUNT() helps analysts measure operational activity, customer volume, and business totals inside organizational datasets. COUNT(*) counts all rows. COUNT(column) skips NULLs. Combine with WHERE to count filtered subsets. COUNT() always returns an integer.
