GROUP BY with multiple columns in MySQL - Mini Project: Build & Apply

GROUP BY with multiple columns in MySQL

📖 Scenario: You work for a small bookstore that wants to analyze its sales data. The store records each sale with the book's genre, the month of sale, and the number of copies sold.The manager wants to see the total copies sold grouped by both genre and month to understand which genres sell best in which months.

🎯 Goal: Create a MySQL query that groups sales data by both genre and month, and calculates the total copies sold for each group.

📋 What You'll Learn

Create a table called book_sales with columns genre (VARCHAR), month (VARCHAR), and copies_sold (INT).

Insert the exact sales data provided into the book_sales table.

Write a SELECT query that groups the data by genre and month.

Calculate the total copies sold for each group using SUM(copies_sold).

Order the results by genre ascending and month ascending.

💡 Why This Matters

🌍 Real World

Grouping sales data by multiple columns helps businesses analyze trends across different categories and time periods.

💼 Career

Understanding GROUP BY with multiple columns is essential for data analysis roles, report generation, and database querying in many jobs.

1

Create the book_sales table and insert data

Create a table called book_sales with columns genre as VARCHAR(20), month as VARCHAR(10), and copies_sold as INT. Then insert these exact rows into book_sales: ('Fiction', 'January', 120), ('Fiction', 'February', 100), ('Non-Fiction', 'January', 80), ('Non-Fiction', 'February', 90), ('Science', 'January', 50), ('Science', 'February', 60).

MySQL

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

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows exactly as given.

2

Set up the SELECT statement with grouping columns

Start writing a SELECT query that selects genre and month from the book_sales table. Add a GROUP BY clause that groups by both genre and month.

MySQL

CREATE TABLE book_sales (
  genre VARCHAR(20),
  month VARCHAR(10),
  copies_sold INT
);

INSERT INTO book_sales (genre, month, copies_sold) VALUES
('Fiction', 'January', 120),
('Fiction', 'February', 100),
('Non-Fiction', 'January', 80),
('Non-Fiction', 'February', 90),
('Science', 'January', 50),
('Science', 'February', 60);

-- Write SELECT genre, month and GROUP BY genre, month
-- Your code here

Hint

Use GROUP BY genre, month to group by both columns.

3

Add the aggregation to calculate total copies sold

Modify the SELECT query to include SUM(copies_sold) AS total_copies to calculate the total copies sold for each group of genre and month.

MySQL

CREATE TABLE book_sales (
  genre VARCHAR(20),
  month VARCHAR(10),
  copies_sold INT
);

INSERT INTO book_sales (genre, month, copies_sold) VALUES
('Fiction', 'January', 120),
('Fiction', 'February', 100),
('Non-Fiction', 'January', 80),
('Non-Fiction', 'February', 90),
('Science', 'January', 50),
('Science', 'February', 60);

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

Hint

Use SUM(copies_sold) AS total_copies to get the total copies sold per group.

4

Order the results by genre and month

Add an ORDER BY clause to the SELECT query to sort the results by genre ascending and then by month ascending.

MySQL

CREATE TABLE book_sales (
  genre VARCHAR(20),
  month VARCHAR(10),
  copies_sold INT
);

INSERT INTO book_sales (genre, month, copies_sold) VALUES
('Fiction', 'January', 120),
('Fiction', 'February', 100),
('Non-Fiction', 'January', 80),
('Non-Fiction', 'February', 90),
('Science', 'January', 50),
('Science', 'February', 60);

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

Hint

Use ORDER BY genre ASC, month ASC to sort the results.

Practice

1. What does the GROUP BY clause with multiple columns do in a MySQL query?

easy

A. It groups rows based on only the first column listed.

B. It groups rows based on the combination of values in all specified columns.

C. It sorts the rows without grouping them.

D. It filters rows that have duplicate values in any column.

Solution

1. Step 1: Understand GROUP BY behavior

   The GROUP BY clause groups rows that have the same values in the specified columns together.

2. Step 2: Multiple columns effect

   When multiple columns are listed, grouping happens on the combined values of all those columns, not individually.

3. Final Answer:

   It groups rows based on the combination of values in all specified columns. -> Option B

4. Quick Check:

   GROUP BY multiple columns groups by combined values [OK]

Hint: GROUP BY multiple columns groups by combined column values [OK]

Common Mistakes:

• Thinking it groups by each column separately
• Confusing GROUP BY with ORDER BY
• Assuming it filters duplicates instead of grouping

2. Which of the following is the correct syntax to group data by columns city and department in MySQL?

easy

A. SELECT city, department FROM employees GROUP BY city, department;

B. SELECT city, department FROM employees GROUP city, department;

C. SELECT city, department FROM employees GROUP BY city department;

D. SELECT city, department FROM employees GROUP BY (city, department);

Solution

1. Step 1: Identify correct GROUP BY syntax

   The correct syntax uses GROUP BY followed by column names separated by commas.

2. Step 2: Check each option

   SELECT city, department FROM employees GROUP BY city, department; uses correct syntax with commas. Options A, B, and D have syntax errors or wrong keywords.

3. Final Answer:

   SELECT city, department FROM employees GROUP BY city, department; -> Option A

4. Quick Check:

   GROUP BY columns separated by commas [OK]

Hint: Use commas to separate columns in GROUP BY [OK]

Common Mistakes:

• Omitting commas between columns
• Using wrong keywords like GROUP instead of GROUP BY
• Using parentheses incorrectly around columns

3. Given the table sales with columns region, product, and amount, what will this query return?

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

medium

A. Syntax error due to missing alias for SUM(amount).

B. Total sales amount for each region only.

C. Total sales amount for each product only.

D. Total sales amount for each region and product combination.

Solution

1. Step 1: Understand GROUP BY with multiple columns

   The query groups rows by both region and product together.

2. Step 2: Apply SUM aggregation

   Within each group, it sums the amount values, giving total sales per region-product pair.

3. Final Answer:

   Total sales amount for each region and product combination. -> Option D

4. Quick Check:

   GROUP BY multiple columns sums per combined group [OK]

Hint: GROUP BY multiple columns sums per combined group [OK]

Common Mistakes:

• Thinking it sums only by one column
• Expecting syntax error without alias (alias optional)
• Confusing GROUP BY with WHERE filtering

4. Identify the error in this query:

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

medium

A. Missing department in GROUP BY causes error or incorrect grouping.

B. COUNT(*) cannot be used with GROUP BY.

C. SELECT must include only aggregated columns.

D. GROUP BY should be after WHERE clause.

Solution

1. Step 1: Check SELECT and GROUP BY columns

   The query selects city and department but groups only by city.

2. Step 2: Understand grouping rules

   All non-aggregated columns in SELECT must appear in GROUP BY to avoid errors or unpredictable results.

3. Final Answer:

   Missing department in GROUP BY causes error or incorrect grouping. -> Option A

4. Quick Check:

   All selected non-aggregated columns must be in GROUP BY [OK]

Hint: Include all non-aggregated SELECT columns in GROUP BY [OK]

Common Mistakes:

• Selecting columns not in GROUP BY without aggregation
• Thinking COUNT(*) is invalid with GROUP BY
• Misplacing GROUP BY clause

5. You have a transactions table with columns store_id, employee_id, and sales_amount. You want to find the total sales per store and employee but only for employees with total sales above 1000. Which query correctly achieves this?

hard

A. SELECT store_id, employee_id, SUM(sales_amount) FROM transactions GROUP BY store_id HAVING SUM(sales_amount) > 1000;

B. SELECT store_id, employee_id, SUM(sales_amount) FROM transactions WHERE SUM(sales_amount) > 1000 GROUP BY store_id, employee_id;

C. SELECT store_id, employee_id, SUM(sales_amount) FROM transactions GROUP BY store_id, employee_id HAVING SUM(sales_amount) > 1000;

D. SELECT store_id, employee_id, SUM(sales_amount) FROM transactions GROUP BY employee_id, store_id WHERE SUM(sales_amount) > 1000;

Solution

1. Step 1: Group by both store_id and employee_id

   We need totals per store and employee, so GROUP BY both columns together.

2. Step 2: Filter groups with HAVING clause

   Use HAVING to filter groups where total sales exceed 1000. WHERE cannot filter aggregated results.

3. Final Answer:

   SELECT store_id, employee_id, SUM(sales_amount) FROM transactions GROUP BY store_id, employee_id HAVING SUM(sales_amount) > 1000; -> Option C

4. Quick Check:

   Use HAVING to filter aggregated groups [OK]

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

Common Mistakes:

• Using WHERE to filter aggregated results
• Grouping by only one column instead of both
• Placing WHERE after GROUP BY