How GROUP BY changes query execution in SQL - Try It Yourself

How GROUP BY changes query execution

📖 Scenario: You work at a small bookstore that keeps track of sales in a database table called sales. Each sale records the book_title, the genre of the book, and the quantity sold.Your manager wants to understand how many books were sold in total for each genre to decide which genres to promote.

🎯 Goal: Build a SQL query that groups sales by genre and calculates the total quantity sold for each genre using GROUP BY.

📋 What You'll Learn

Create a table called sales with columns book_title (text), genre (text), and quantity (integer).

Insert the exact sales data provided.

Write a query that groups the sales by genre.

Calculate the total quantity sold per genre using SUM(quantity).

Use GROUP BY genre in the query.

💡 Why This Matters

🌍 Real World

Grouping sales data by category helps businesses understand which product types sell best and make informed marketing decisions.

💼 Career

Data analysts and database developers often write GROUP BY queries to summarize and report data efficiently.

1

Create the sales table and insert data

Write SQL statements to create a table called sales with columns book_title (text), genre (text), and quantity (integer). Then insert these exact rows into sales: ('The Hobbit', 'Fantasy', 5), ('1984', 'Dystopian', 8), ('The Silmarillion', 'Fantasy', 3), ('Brave New World', 'Dystopian', 6), ('The Catcher in the Rye', 'Classic', 4).

SQL

-- Create the 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

Add a variable for the aggregation column

Define a variable or alias called total_quantity that will hold the sum of quantity for each group. This will be used in the next query step.

SQL

CREATE TABLE sales (
    book_title TEXT,
    genre TEXT,
    quantity INTEGER
);

INSERT INTO sales (book_title, genre, quantity) VALUES
('The Hobbit', 'Fantasy', 5),
('1984', 'Dystopian', 8),
('The Silmarillion', 'Fantasy', 3),
('Brave New World', 'Dystopian', 6),
('The Catcher in the Rye', 'Classic', 4);

-- Define an alias for sum(quantity) as total_quantity
-- Your code here

Hint

In SQL, you create an alias for the sum inside the SELECT statement using SUM(quantity) AS total_quantity. This will be done in the next step.

3

Write the query to group sales by genre and sum quantity

Write a SQL query that selects genre and the sum of quantity as total_quantity from the sales table. Use GROUP BY genre to group the rows by genre.

SQL

CREATE TABLE sales (
    book_title TEXT,
    genre TEXT,
    quantity INTEGER
);

INSERT INTO sales (book_title, genre, quantity) VALUES
('The Hobbit', 'Fantasy', 5),
('1984', 'Dystopian', 8),
('The Silmarillion', 'Fantasy', 3),
('Brave New World', 'Dystopian', 6),
('The Catcher in the Rye', 'Classic', 4);

SELECT genre, SUM(quantity) AS total_quantity
FROM sales
GROUP BY genre;
-- Your code here

Hint

Use SELECT genre, SUM(quantity) AS total_quantity FROM sales GROUP BY genre to group and sum.

4

Complete the query with ordering by total quantity

Add an ORDER BY clause to the query to sort the results by total_quantity in descending order, so the genre with the highest sales appears first.

SQL

CREATE TABLE sales (
    book_title TEXT,
    genre TEXT,
    quantity INTEGER
);

INSERT INTO sales (book_title, genre, quantity) VALUES
('The Hobbit', 'Fantasy', 5),
('1984', 'Dystopian', 8),
('The Silmarillion', 'Fantasy', 3),
('Brave New World', 'Dystopian', 6),
('The Catcher in the Rye', 'Classic', 4);

SELECT genre, SUM(quantity) AS total_quantity
FROM sales
GROUP BY genre
ORDER BY total_quantity DESC;
-- Your code here

Hint

Use ORDER BY total_quantity DESC to sort from highest to lowest total quantity.

Practice

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

easy

A. It filters rows based on a condition.

B. It groups rows that have the same values in specified columns.

C. It deletes duplicate rows from the result.

D. It sorts the rows in ascending order.

Solution

1. Step 1: Understand the purpose of GROUP BY

   The GROUP BY clause collects rows with the same values in specified columns into groups.

2. Step 2: Compare with other SQL clauses

   Sorting is done by ORDER BY, filtering by WHERE, and removing duplicates by DISTINCT, not GROUP BY.

3. Final Answer:

   It groups rows that have the same values in specified columns. -> Option B

4. Quick Check:

   GROUP BY groups rows by column values [OK]

Hint: GROUP BY groups rows by column values, not sorting or filtering [OK]

Common Mistakes:

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

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

easy

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

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

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

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

Solution

1. Step 1: Identify correct GROUP BY usage

   The GROUP BY clause must follow the FROM clause and specify the column to group by, here 'department'.

2. Step 2: Check each option's syntax

   SELECT department, COUNT(*) FROM employees GROUP BY department; uses GROUP BY correctly. SELECT department, COUNT(*) FROM employees ORDER BY department; uses ORDER BY which sorts, not groups. SELECT department, COUNT(*) FROM employees WHERE department; uses WHERE incorrectly. SELECT department, COUNT(*) FROM employees HAVING department; uses HAVING without GROUP BY, which is invalid.

3. Final Answer:

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

4. Quick Check:

   GROUP BY syntax: SELECT ... FROM ... GROUP BY column [OK]

Hint: GROUP BY follows FROM and lists columns to group by [OK]

Common Mistakes:

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

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 regions with the total sales amount per region.

B. A list of all sales amounts without grouping.

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 aggregate functions

   The query groups rows by 'region' and calculates the sum of 'amount' for each group.

2. Step 2: Analyze the output

   The result shows each region once with the total sales amount summed from all rows in that region.

3. Final Answer:

   A list of regions with the total sales amount per region. -> Option A

4. Quick Check:

   GROUP BY + SUM() = totals per group [OK]

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

Common Mistakes:

• Expecting all rows without grouping
• Thinking SUM() causes error with GROUP BY
• Confusing grouping with sorting

4. What is wrong with this query?

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

medium

A. You cannot select employee_name without grouping by it or using an aggregate function.

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

C. GROUP BY must come before SELECT.

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

Solution

1. Step 1: Check columns in SELECT with GROUP BY

   When using GROUP BY on 'department', all selected columns must be grouped or aggregated.

2. Step 2: Identify the error

   'employee_name' is neither grouped nor aggregated, causing a syntax error.

3. Final Answer:

   You cannot select employee_name without grouping by it or using an aggregate function. -> Option A

4. Quick Check:

   Non-grouped columns must be aggregated [OK]

Hint: All selected columns must be grouped or aggregated [OK]

Common Mistakes:

• Selecting non-grouped columns without aggregation
• Thinking COUNT(*) is invalid with GROUP BY
• Misplacing GROUP BY clause

5. You want to find departments with more than 5 employees. Which query correctly uses GROUP BY and HAVING to achieve this?

hard

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

B. SELECT department, COUNT(*) FROM employees HAVING COUNT(*) > 5 GROUP BY department;

C. SELECT department, COUNT(*) FROM employees WHERE COUNT(*) > 5 GROUP BY department;

D. SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;

Solution

1. Step 1: Understand HAVING clause usage

   HAVING filters groups after aggregation, so it must come after GROUP BY.

2. Step 2: Check query order and syntax

   SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5; correctly places HAVING after GROUP BY with condition COUNT(*) > 5. Other options misuse HAVING or WHERE clauses.

3. Final Answer:

   SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5; -> Option D

4. Quick Check:

   HAVING filters groups after GROUP BY [OK]

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

Common Mistakes:

• Using WHERE to filter aggregated results
• Placing HAVING before GROUP BY
• Confusing WHERE and HAVING clauses