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.