GROUP BY multiple columns in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of GROUP BY on two columns

Given the table Sales with columns region, product, and amount, what is the output of this query?

SELECT region, product, SUM(amount) AS total_sales
FROM Sales
GROUP BY region, product
ORDER BY region, product;

SQL

CREATE TABLE Sales (region VARCHAR(10), product VARCHAR(10), amount INT);
INSERT INTO Sales VALUES
('East', 'Pen', 10),
('East', 'Pen', 15),
('East', 'Pencil', 5),
('West', 'Pen', 20),
('West', 'Pencil', 10);

A [{"region": "East", "product": "Pen", "total_sales": 15}, {"region": "East", "product": "Pencil", "total_sales": 5}, {"region": "West", "product": "Pen", "total_sales": 20}, {"region": "West", "product": "Pencil", "total_sales": 10}]

B [{"region": "East", "product": "Pen", "total_sales": 25}, {"region": "East", "product": "Pencil", "total_sales": 5}, {"region": "West", "product": "Pen", "total_sales": 20}, {"region": "West", "product": "Pencil", "total_sales": 10}]

C [{"region": "East", "product": "Pen", "total_sales": 25}, {"region": "East", "product": "Pencil", "total_sales": 10}, {"region": "West", "product": "Pen", "total_sales": 20}, {"region": "West", "product": "Pencil", "total_sales": 10}]

D [{"region": "East", "product": "Pen", "total_sales": 25}, {"region": "West", "product": "Pen", "total_sales": 20}]

💡 Hint

GROUP BY groups rows that have the same values in all listed columns.

The query groups rows by both region and product. For example, 'East' and 'Pen' rows are summed together (10 + 15 = 25). Other groups are summed similarly.

🧠 Conceptual

intermediate

Understanding GROUP BY multiple columns

Why do we use multiple columns in a GROUP BY clause in SQL?

A To join multiple tables based on those columns.

B To filter rows before grouping them.

C To group rows that have the same values in all specified columns together.

D To sort the results after grouping.

💡 Hint

Think about how grouping works with one column, then extend to multiple columns.

Using multiple columns in GROUP BY groups rows that share the same values in all those columns, creating finer groups.

📝 Syntax

advanced

Identify the correct GROUP BY syntax with multiple columns

Which of the following SQL queries correctly groups by two columns category and year?

A SELECT category, year, COUNT(*) FROM sales GROUP BY category, year;

B SELECT category, year, COUNT(*) FROM sales GROUP BY category year;

C SELECT category, year, COUNT(*) FROM sales GROUP BY (category, year);

D SELECT category, year, COUNT(*) FROM sales GROUP BY category; year;

💡 Hint

Check the syntax for separating columns in GROUP BY.

Columns in GROUP BY must be separated by commas without parentheses or semicolons.

❓ optimization

advanced

Optimizing GROUP BY with multiple columns

You have a large table with columns country, city, and sales. Which index would best improve performance of this query?

SELECT country, city, SUM(sales) FROM sales_data GROUP BY country, city;

A No index is needed for GROUP BY queries.

B Create separate indexes on country and city individually.

C Create an index on sales column.

D Create a composite index on (country, city).

💡 Hint

Think about how the database groups rows efficiently.

A composite index on the grouped columns helps the database quickly find and group rows by those columns.

🔧 Debug

expert

Why does this GROUP BY query cause an error?

Consider this query:

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

Why does it cause an error?

A Because employee is not in the GROUP BY clause or an aggregate function.

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

C Because department is not a valid column.

D Because GROUP BY must include all columns in SELECT.

💡 Hint

Check which columns must appear in GROUP BY or be aggregated.

Columns in SELECT that are not aggregated must appear in GROUP BY. Here, employee is missing from GROUP BY and not aggregated.

Practice

1. What does the SQL clause GROUP BY column1, column2 do?

easy

A. Sorts the table by column1 and then column2

B. Groups rows by unique combinations of values in column1 and column2

C. Filters rows where column1 equals column2

D. Joins two tables on column1 and column2

Solution

1. Step 1: Understand GROUP BY purpose

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

2. Step 2: Apply to multiple columns

   When multiple columns are listed, grouping happens on unique combinations of those columns' values.

3. Final Answer:

   Groups rows by unique combinations of values in column1 and column2 -> Option B

4. Quick Check:

   GROUP BY multiple columns = group by combinations [OK]

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

Common Mistakes:

• Thinking GROUP BY sorts data
• Confusing GROUP BY with WHERE filtering
• Assuming GROUP BY joins tables

2. Which of the following is the correct syntax to group data by two columns named city and year?

easy

A. SELECT city, year FROM table GROUP city, year;

B. SELECT city, year FROM table ORDER BY city, year;

C. SELECT city, year FROM table GROUP BY city, year;

D. SELECT city, year FROM table GROUP BY city year;

Solution

1. Step 1: Recall GROUP BY syntax

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

2. Step 2: Check each option

   SELECT city, year FROM table GROUP BY city, year; uses correct syntax with commas. SELECT city, year FROM table ORDER BY city, year; uses ORDER BY, which is for sorting. SELECT city, year FROM table GROUP city, year; misses BY keyword. SELECT city, year FROM table GROUP BY city year; misses comma between columns.

3. Final Answer:

   SELECT city, year FROM table GROUP BY city, year; -> Option C

4. Quick Check:

   GROUP BY columns separated by commas [OK]

Hint: Use GROUP BY with commas between columns [OK]

Common Mistakes:

• Using ORDER BY instead of GROUP BY
• Omitting BY keyword after GROUP
• Missing commas between column names

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. Total sales amount for each region only

B. Syntax error due to missing GROUP BY columns

C. Total sales amount for each product only

D. Total sales amount for each region and product combination

Solution

1. Step 1: Analyze SELECT and GROUP BY columns

   The query groups rows by both region and product, so each group is a unique pair of region and product.

2. Step 2: Understand aggregation function SUM(amount)

   SUM(amount) calculates total sales amount for each group of region and product.

3. Final Answer:

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

4. Quick Check:

   GROUP BY region, product + SUM = totals per pair [OK]

Hint: GROUP BY columns + SUM aggregates per group [OK]

Common Mistakes:

• Thinking it sums only by one column
• Assuming syntax error without reason
• Ignoring that all selected non-aggregated columns must be grouped

4. Identify the error in this query:

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

medium

A. Missing role column in GROUP BY clause

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

C. department should not be in GROUP BY

D. SELECT must include only aggregated columns

Solution

1. Step 1: Check SELECT columns vs GROUP BY columns

   Columns in SELECT that are not aggregated must appear in GROUP BY. Here, role is in SELECT but missing in GROUP BY.

2. Step 2: Understand aggregation rules

   COUNT(*) is valid, but all non-aggregated columns must be grouped to avoid errors.

3. Final Answer:

   Missing role column in GROUP BY clause -> Option A

4. Quick Check:

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

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

Common Mistakes:

• Ignoring missing columns in GROUP BY
• Thinking COUNT(*) is invalid with GROUP BY
• Assuming GROUP BY only needs one column

5. You have a transactions table with columns customer_id, month, and amount. You want to find the average transaction amount per customer per month, but only for months where the customer made more than 3 transactions. Which query correctly achieves this?

hard

A. SELECT customer_id, month, AVG(amount) FROM transactions GROUP BY customer_id, month HAVING COUNT(*) > 3;

B. SELECT customer_id, month, AVG(amount) FROM transactions WHERE COUNT(*) > 3 GROUP BY customer_id, month;

C. SELECT customer_id, month, AVG(amount) FROM transactions GROUP BY customer_id HAVING COUNT(*) > 3;

D. SELECT customer_id, month, AVG(amount) FROM transactions GROUP BY month HAVING COUNT(*) > 3;

Solution

1. Step 1: Understand filtering groups with HAVING

   HAVING filters groups after grouping. To filter groups with more than 3 transactions, use HAVING COUNT(*) > 3.

2. Step 2: Group by both customer_id and month

   To get average per customer per month, group by both columns.

3. Step 3: Check each option

   SELECT customer_id, month, AVG(amount) FROM transactions GROUP BY customer_id, month HAVING COUNT(*) > 3; correctly uses GROUP BY customer_id, month and HAVING COUNT(*) > 3. SELECT customer_id, month, AVG(amount) FROM transactions WHERE COUNT(*) > 3 GROUP BY customer_id, month; misuses WHERE with COUNT(). SELECT customer_id, month, AVG(amount) FROM transactions GROUP BY customer_id HAVING COUNT(*) > 3; groups only by customer_id, missing month. SELECT customer_id, month, AVG(amount) FROM transactions GROUP BY month HAVING COUNT(*) > 3; groups only by month, missing customer_id.

4. Final Answer:

   SELECT customer_id, month, AVG(amount) FROM transactions GROUP BY customer_id, month HAVING COUNT(*) > 3; -> Option A

5. Quick Check:

   Use HAVING to filter grouped counts [OK]

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

Common Mistakes:

• Using WHERE with aggregate functions
• Grouping by only one column when two needed
• Filtering before grouping instead of after