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

Need a 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

Need a 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

Need a 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

Need a hint?

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