GROUP BY single column in SQL - Mini Project: Build & Apply

Using GROUP BY on a Single Column in SQL

📖 Scenario: You work at a small bookstore. You have a table that records every book sale with the book's genre and the number of copies sold. You want to find out how many copies were sold for each genre.

🎯 Goal: Create an SQL query that groups the sales by the genre column and sums the copies_sold for each genre.

📋 What You'll Learn

Create a table called book_sales with columns genre (text) and copies_sold (integer).

Insert the exact rows given into book_sales.

Write a query that groups the data by genre and sums copies_sold.

Order the results by genre alphabetically.

💡 Why This Matters

🌍 Real World

Grouping sales data by category helps businesses understand which categories perform best.

💼 Career

Data analysts and database developers often use GROUP BY to summarize data for reports.

1

Create the book_sales table and insert data

Write SQL statements to create a table called book_sales with columns genre (text) and copies_sold (integer). Then insert these exact rows: ('Fiction', 10), ('Non-Fiction', 5), ('Fiction', 7), ('Science', 3), ('Non-Fiction', 8).

SQL

-- Create table book_sales and insert rows
-- Your code here

Hint

Use CREATE TABLE to make the table, then INSERT INTO to add rows.

2

Set up the SELECT statement with columns

Write a SQL SELECT statement that selects the genre column and the sum of copies_sold as total_copies from the book_sales table.

SQL

CREATE TABLE book_sales (
    genre TEXT,
    copies_sold INTEGER
);

INSERT INTO book_sales (genre, copies_sold) VALUES
('Fiction', 10),
('Non-Fiction', 5),
('Fiction', 7),
('Science', 3),
('Non-Fiction', 8);

-- Write SELECT statement with genre and sum of copies_sold
-- Your code here

Hint

Use SUM() to add up copies sold and give it an alias total_copies.

3

Add the GROUP BY clause

Add a GROUP BY genre clause to the existing SELECT statement to group the sales by genre.

SQL

CREATE TABLE book_sales (
    genre TEXT,
    copies_sold INTEGER
);

INSERT INTO book_sales (genre, copies_sold) VALUES
('Fiction', 10),
('Non-Fiction', 5),
('Fiction', 7),
('Science', 3),
('Non-Fiction', 8);

SELECT genre, SUM(copies_sold) AS total_copies
FROM book_sales
-- Add GROUP BY genre below
-- Your code here

Hint

Use GROUP BY genre to group rows by the genre column.

4

Order the results by genre alphabetically

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

SQL

CREATE TABLE book_sales (
    genre TEXT,
    copies_sold INTEGER
);

INSERT INTO book_sales (genre, copies_sold) VALUES
('Fiction', 10),
('Non-Fiction', 5),
('Fiction', 7),
('Science', 3),
('Non-Fiction', 8);

SELECT genre, SUM(copies_sold) AS total_copies
FROM book_sales
GROUP BY genre
-- Add ORDER BY genre ASC below
-- Your code here

Hint

Use ORDER BY genre ASC to sort genres alphabetically.

Practice

1. What does the GROUP BY clause do in an SQL query?

easy

A. It deletes duplicate rows from the table.

B. It sorts the rows in ascending order.

C. It groups rows that have the same values in a specified column.

D. It filters rows based on a condition.

Solution

1. Step 1: Understand the purpose of GROUP BY

   The GROUP BY clause collects rows with the same value in the specified column into groups.

2. Step 2: Differentiate from other clauses

   Unlike ORDER BY which sorts, or WHERE which filters, GROUP BY organizes data for aggregation.

3. Final Answer:

   It groups rows that have the same values in a specified column. -> Option C

4. Quick Check:

   GROUP BY = grouping rows by column [OK]

Hint: GROUP BY collects rows sharing column values [OK]

Common Mistakes:

• Confusing GROUP BY with ORDER BY
• Thinking GROUP BY filters rows
• Assuming GROUP BY deletes duplicates

2. Which of the following is the correct syntax to group data by the column department?

easy

A. SELECT department, COUNT(*) FROM employees GROUP BY department;

B. SELECT department, COUNT(*) FROM employees ORDER BY department;

C. SELECT department, COUNT(*) FROM employees WHERE department;

D. SELECT department, COUNT(*) FROM employees HAVING department;

Solution

1. Step 1: Identify correct GROUP BY usage

   The GROUP BY clause must follow FROM and group by the column named.

2. Step 2: Check each option's clause

   SELECT department, COUNT(*) FROM employees GROUP BY department; uses GROUP BY correctly; others use ORDER BY, WHERE, HAVING incorrectly here.

3. Final Answer:

   SELECT department, COUNT(*) FROM employees GROUP BY department; -> Option A

4. Quick Check:

   GROUP BY syntax = SELECT ... GROUP BY column [OK]

Hint: GROUP BY follows FROM and groups by column [OK]

Common Mistakes:

• Using ORDER BY instead of GROUP BY
• Using WHERE to group data
• Using HAVING without aggregation

3. Given the table sales with columns region and amount, what is the output of this query?

SELECT region, SUM(amount) FROM sales GROUP BY region;

medium

A. A list of all sales amounts without grouping.

B. A list of regions with the total sales amount for each region.

C. An error because SUM() cannot be used with GROUP BY.

D. A list of regions sorted by amount.

Solution

1. Step 1: Understand GROUP BY with SUM()

   The query groups rows by region and sums the amount for each group.

2. Step 2: Predict output format

   The output shows each region once with the total amount of sales in that region.

3. Final Answer:

   A list of regions with the total sales amount for each region. -> Option B

4. Quick Check:

   GROUP BY + SUM() = grouped sums [OK]

Hint: GROUP BY + SUM() gives totals per group [OK]

Common Mistakes:

• Thinking SUM() can't be used with GROUP BY
• Expecting ungrouped list
• Confusing sorting with grouping

4. Identify the error in this SQL query:

SELECT department, COUNT(*) FROM employees;

medium

A. The query is correct and will run without errors.

B. COUNT(*) cannot be used without WHERE clause.

C. department cannot be selected without aggregation.

D. Missing GROUP BY clause for the department column.

Solution

1. Step 1: Check SELECT with aggregation

   COUNT(*) is an aggregate but department is not aggregated or grouped.

2. Step 2: Identify missing GROUP BY

   To select department with COUNT(*), GROUP BY department is required.

3. Final Answer:

   Missing GROUP BY clause for the department column. -> Option D

4. Quick Check:

   Non-aggregated columns need GROUP BY [OK]

Hint: Non-aggregated columns need GROUP BY [OK]

Common Mistakes:

• Ignoring missing GROUP BY
• Thinking COUNT(*) needs WHERE
• Assuming query runs without error

5. You have a table orders with columns customer_id, order_date, and total. You want to find the average order total per customer but only for customers who have placed more than 3 orders. Which query correctly achieves this?

hard

A. SELECT customer_id, AVG(total) FROM orders GROUP BY customer_id HAVING COUNT(*) > 3;

B. SELECT customer_id, AVG(total) FROM orders WHERE COUNT(*) > 3 GROUP BY customer_id;

C. SELECT customer_id, AVG(total) FROM orders GROUP BY customer_id WHERE COUNT(*) > 3;

D. SELECT customer_id, AVG(total) FROM orders HAVING COUNT(*) > 3 GROUP BY customer_id;

Solution

1. Step 1: Use GROUP BY to group orders by customer_id

   This groups all orders per customer to calculate aggregates.

2. Step 2: Use HAVING to filter groups with more than 3 orders

   HAVING filters groups after aggregation; WHERE cannot filter aggregates.

3. Final Answer:

   SELECT customer_id, AVG(total) FROM orders GROUP BY customer_id HAVING COUNT(*) > 3; -> Option A

4. Quick Check:

   HAVING filters groups, WHERE filters rows [OK]

Hint: Use HAVING to filter groups after GROUP BY [OK]

Common Mistakes:

• Using WHERE to filter aggregated counts
• Placing HAVING before GROUP BY
• Not filtering groups at all