COUNT function in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

COUNT with NULL values

Given the table Employees with columns id, name, and manager_id where some manager_id values are NULL, what is the result of this query?

SELECT COUNT(manager_id) FROM Employees;

MySQL

CREATE TABLE Employees (id INT, name VARCHAR(50), manager_id INT);
INSERT INTO Employees VALUES (1, 'Alice', NULL), (2, 'Bob', 1), (3, 'Charlie', NULL), (4, 'Diana', 2);

A 2

B 4

C 3

D 1

💡 Hint

COUNT(column) counts only non-NULL values in that column.

COUNT(manager_id) counts only rows where manager_id is not NULL. There are 2 such rows: Bob (manager_id=1) and Diana (manager_id=2).

❓ query_result

intermediate

COUNT(*) vs COUNT(column)

Consider the table Orders with columns order_id, customer_id, and shipped_date. Some shipped_date values are NULL. What is the output of this query?

SELECT COUNT(*), COUNT(shipped_date) FROM Orders;

MySQL

CREATE TABLE Orders (order_id INT, customer_id INT, shipped_date DATE);
INSERT INTO Orders VALUES (1, 101, '2024-01-01'), (2, 102, NULL), (3, 103, '2024-01-03'), (4, 104, NULL);

A 4, 2

B 2, 4

C 4, 4

D 2, 2

💡 Hint

COUNT(*) counts all rows, COUNT(column) counts only non-NULL values in that column.

COUNT(*) counts all 4 rows. COUNT(shipped_date) counts only the 2 rows where shipped_date is not NULL.

📝 Syntax

advanced

Invalid use of COUNT in WHERE clause

Which option shows the correct way to count rows with a condition on the count itself?
Given a table Sales with product_id and quantity, you want to find products sold more than 10 times.
Which query is valid?

A SELECT product_id FROM Sales WHERE SUM(quantity) > 10;

B SELECT product_id FROM Sales GROUP BY product_id WHERE COUNT(quantity) > 10;

C SELECT product_id FROM Sales GROUP BY product_id HAVING COUNT(quantity) > 10;

D SELECT product_id FROM Sales WHERE COUNT(quantity) > 10;

💡 Hint

Conditions on aggregate functions must use HAVING, not WHERE.

COUNT is an aggregate function and cannot be used in WHERE. HAVING filters groups after aggregation.

❓ optimization

advanced

Optimizing COUNT with large tables

You have a large table Visits with millions of rows. You want to count how many visits happened today. Which query is the most efficient?

A SELECT COUNT(visit_date) FROM Visits WHERE visit_date = CURDATE();

B SELECT COUNT(DISTINCT visit_date) FROM Visits WHERE visit_date = CURDATE();

C SELECT COUNT(*) FROM Visits;

D SELECT COUNT(*) FROM Visits WHERE visit_date = CURDATE();

💡 Hint

COUNT(*) is optimized to count rows quickly. Filtering by date reduces scanned rows.

Option D counts all rows matching today's date efficiently. COUNT(visit_date) is similar but COUNT(*) is preferred for counting rows. Option D counts all rows ignoring date. Option D counts distinct dates, not visits.

🧠 Conceptual

expert

COUNT with JOIN and NULLs

Given two tables:

• Authors(id, name)
• Books(id, author_id, title)

You want to count how many books each author has, including authors with zero books.
Which query produces the correct result?

A SELECT Authors.name, COUNT(Books.id) FROM Authors JOIN Books ON Authors.id = Books.author_id GROUP BY Authors.name;

B SELECT Authors.name, COUNT(Books.id) FROM Authors LEFT JOIN Books ON Authors.id = Books.author_id GROUP BY Authors.name;

C SELECT Authors.name, COUNT(*) FROM Authors LEFT JOIN Books ON Authors.id = Books.author_id GROUP BY Authors.name;

D SELECT Authors.name, COUNT(Books.author_id) FROM Authors RIGHT JOIN Books ON Authors.id = Books.author_id GROUP BY Authors.name;

💡 Hint

Use LEFT JOIN to include authors without books. COUNT(column) ignores NULLs.

LEFT JOIN keeps all authors. COUNT(Books.id) counts books per author. INNER JOIN (option B) excludes authors with zero books. COUNT(*) counts all joined rows, including NULLs from Books in LEFT JOIN, which can miscount.