COUNT function in MySQL - Mini Project: Build & Apply

Counting Customers by Country with COUNT Function

📖 Scenario: You work for a company that has customers from different countries. You want to find out how many customers are from each country.

🎯 Goal: Build a SQL query that uses the COUNT function to count the number of customers per country.

📋 What You'll Learn

Create a table called customers with columns id, name, and country.

Insert exactly 5 customers with specified names and countries.

Write a SQL query that counts customers grouped by country using the COUNT function.

Order the results by country alphabetically.

💡 Why This Matters

🌍 Real World

Counting customers by country helps businesses understand their market distribution and target marketing efforts.

💼 Career

Database analysts and developers often write queries with COUNT and GROUP BY to summarize data for reports.

1

Create the customers table and insert data

Write SQL statements to create a table called customers with columns id (integer), name (text), and country (text). Then insert these exact rows: (1, 'Alice', 'USA'), (2, 'Bob', 'Canada'), (3, 'Charlie', 'USA'), (4, 'Diana', 'UK'), (5, 'Eve', 'Canada').

MySQL

-- Create the customers table and insert data
-- Your code here

Hint

Use CREATE TABLE to make the table. Use INSERT INTO with multiple rows to add the customers.

2

Set up the query to count customers

Write a SQL SELECT statement that will count the number of customers. Start by selecting country and use COUNT(*) to count customers. Use FROM customers to specify the table.

MySQL

CREATE TABLE customers (
  id INT,
  name VARCHAR(50),
  country VARCHAR(50)
);

INSERT INTO customers (id, name, country) VALUES
(1, 'Alice', 'USA'),
(2, 'Bob', 'Canada'),
(3, 'Charlie', 'USA'),
(4, 'Diana', 'UK'),
(5, 'Eve', 'Canada');

-- Write SELECT statement to count customers by country
-- Your code here

Hint

Use SELECT country, COUNT(*) and specify the table with FROM customers.

3

Group the results by country

Add a GROUP BY country clause to the existing query to group the customers by their country.

MySQL

CREATE TABLE customers (
  id INT,
  name VARCHAR(50),
  country VARCHAR(50)
);

INSERT INTO customers (id, name, country) VALUES
(1, 'Alice', 'USA'),
(2, 'Bob', 'Canada'),
(3, 'Charlie', 'USA'),
(4, 'Diana', 'UK'),
(5, 'Eve', 'Canada');

SELECT country, COUNT(*)
FROM customers
-- Add GROUP BY country below
-- Your code here

Hint

Use GROUP BY country to group the rows by country.

4

Order the results alphabetically by country

Add an ORDER BY country ASC clause to the query to sort the results alphabetically by country.

MySQL

CREATE TABLE customers (
  id INT,
  name VARCHAR(50),
  country VARCHAR(50)
);

INSERT INTO customers (id, name, country) VALUES
(1, 'Alice', 'USA'),
(2, 'Bob', 'Canada'),
(3, 'Charlie', 'USA'),
(4, 'Diana', 'UK'),
(5, 'Eve', 'Canada');

SELECT country, COUNT(*)
FROM customers
GROUP BY country
-- Add ORDER BY country ASC below
-- Your code here

Hint

Use ORDER BY country ASC to sort the results alphabetically by country.

Practice

1. What does the COUNT(*) function do in MySQL?

easy

A. Counts all rows in the table, including those with NULL values

B. Counts only rows where all columns are NOT NULL

C. Counts only rows with NULL values in any column

D. Counts the number of columns in the table

Solution

1. Step 1: Understand COUNT(*) behavior

   The COUNT(*) function counts every row in the table regardless of NULL values.

2. Step 2: Compare with other COUNT variants

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

3. Final Answer:

   Counts all rows in the table, including those with NULL values -> Option A

4. Quick Check:

   COUNT(*) counts all rows [OK]

Hint: COUNT(*) counts every row, NULL or not [OK]

Common Mistakes:

• Thinking COUNT(*) ignores rows with NULL
• Confusing COUNT(*) with COUNT(column)
• Assuming COUNT(*) counts columns

2. Which of the following is the correct syntax to count non-NULL values in the column age from the table users?

easy

A. SELECT COUNT(*) FROM users;

B. SELECT COUNT(age) FROM users WHERE age != NULL;

C. SELECT COUNT(age) FROM users WHERE age = NULL;

D. SELECT COUNT(age) FROM users;

Solution

1. Step 1: Identify correct COUNT syntax for non-NULL values

   COUNT(column) counts only non-NULL values in that column, so COUNT(age) counts non-NULL ages.

2. Step 2: Check WHERE clause correctness

   SELECT COUNT(*) FROM users; uses COUNT(*) which counts all rows. Using WHERE age IS NOT NULL is valid but unnecessary with COUNT(age). Options B and C use incorrect NULL comparisons.

3. Final Answer:

   SELECT COUNT(age) FROM users; -> Option D

4. Quick Check:

   COUNT(column) counts non-NULL values [OK]

Hint: Use COUNT(column) to count non-NULL values directly [OK]

Common Mistakes:

• Using WHERE age = NULL instead of IS NOT NULL
• Using COUNT(*) to count non-NULL values
• Using != NULL which is invalid in SQL

3. Given the table orders with 5 rows where the discount column has 3 NULL values and 2 non-NULL values, what will this query return?

SELECT COUNT(discount) FROM orders;

medium

A. 5

B. 3

C. 2

D. 0

Solution

1. Step 1: Understand COUNT(column) counts non-NULL values

   The query counts only rows where discount is NOT NULL.

2. Step 2: Apply to given data

   There are 2 non-NULL discount values, so the count is 2.

3. Final Answer:

   2 -> Option C

4. Quick Check:

   COUNT(discount) = 2 [OK]

Hint: COUNT(column) ignores NULLs, counts only real values [OK]

Common Mistakes:

• Counting total rows instead of non-NULL values
• Confusing NULL count with non-NULL count
• Assuming COUNT(column) counts NULLs

4. Identify the error in this query:

SELECT COUNT(name) FROM employees WHERE name = NULL;

medium

A. Using COUNT(name) instead of COUNT(*)

B. Incorrect NULL comparison with '=' instead of IS NULL

C. Missing GROUP BY clause

D. COUNT cannot be used with WHERE clause

Solution

1. Step 1: Check WHERE clause NULL comparison

   In SQL, NULL cannot be compared with '='; use IS NULL instead.

2. Step 2: Validate COUNT usage

   COUNT(name) is valid and WHERE clause is allowed; no GROUP BY needed here.

3. Final Answer:

   Incorrect NULL comparison with '=' instead of IS NULL -> Option B

4. Quick Check:

   Use IS NULL for NULL checks [OK]

Hint: Use IS NULL, not = NULL, to check for NULL values [OK]

Common Mistakes:

• Using '=' to compare NULL values
• Thinking COUNT can't be combined with WHERE
• Adding unnecessary GROUP BY

5. You have a table sales with columns region and amount. You want to find how many sales records exist for each region, including regions with zero sales. Which query will correctly show all regions with their sales count, including zero counts?

hard

A. SELECT r.region, COUNT(s.amount) FROM regions r LEFT JOIN sales s ON r.region = s.region GROUP BY r.region;

B. SELECT region, COUNT(*) FROM sales GROUP BY region;

C. SELECT region, COUNT(amount) FROM sales GROUP BY region;

D. SELECT region, COUNT(amount) FROM sales WHERE amount IS NOT NULL GROUP BY region;

Solution

1. Step 1: Understand requirement to include regions with zero sales

   To include regions with zero sales, we must use a LEFT JOIN from all regions to sales.

2. Step 2: Analyze query options

   SELECT r.region, COUNT(s.amount) FROM regions r LEFT JOIN sales s ON r.region = s.region GROUP BY r.region; uses LEFT JOIN from regions table to sales, counting sales per region including zero counts. Options A, B, D only count existing sales records, missing zero counts.

3. Final Answer:

   SELECT r.region, COUNT(s.amount) FROM regions r LEFT JOIN sales s ON r.region = s.region GROUP BY r.region; -> Option A

4. Quick Check:

   LEFT JOIN + COUNT(column) includes zero counts [OK]

Hint: Use LEFT JOIN with COUNT(column) to include zero counts [OK]

Common Mistakes:

• Using only sales table, missing regions with zero sales
• Using COUNT(*) which counts joined rows, not zero
• Not using LEFT JOIN to include all regions