GROUP BY with multiple columns in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of GROUP BY with 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;

MySQL

CREATE TABLE Sales (region VARCHAR(10), product VARCHAR(10), amount INT);
INSERT INTO Sales VALUES
('North', 'A', 100),
('North', 'B', 150),
('South', 'A', 200),
('South', 'B', 100),
('North', 'A', 50);

A [{"region": "North", "product": "A", "total_sales": 100}, {"region": "North", "product": "B", "total_sales": 150}, {"region": "South", "product": "A", "total_sales": 200}, {"region": "South", "product": "B", "total_sales": 100}]

B [{"region": "North", "product": "A", "total_sales": 150}, {"region": "North", "product": "B", "total_sales": 250}, {"region": "South", "product": "A", "total_sales": 200}, {"region": "South", "product": "B", "total_sales": 100}]

C [{"region": "North", "product": "A", "total_sales": 150}, {"region": "North", "product": "B", "total_sales": 150}, {"region": "South", "product": "A", "total_sales": 200}, {"region": "South", "product": "B", "total_sales": 100}]

D [{"region": "North", "product": "A", "total_sales": 150}, {"region": "South", "product": "A", "total_sales": 200}]

💡 Hint

Remember that GROUP BY groups rows by all listed columns together.

The query groups rows by both region and product. For example, 'North' and 'A' rows are combined, summing amounts 100 + 50 = 150.

📝 Syntax

intermediate

Identify the syntax error in GROUP BY with multiple columns

Which option contains a syntax error in the GROUP BY clause when grouping by multiple columns?

MySQL

SELECT region, product, COUNT(*) FROM Sales GROUP BY region product;

A GROUP BY region product

B GROUP BY region, product

C GROUP BY (region, product)

D GROUP BY region; product

💡 Hint

Multiple columns in GROUP BY must be separated by commas.

Option A misses the comma between columns, causing a syntax error.

❓ optimization

advanced

Optimizing GROUP BY with multiple columns

You have a large table Orders with columns customer_id, order_date, and amount. You run this query:

SELECT customer_id, YEAR(order_date) AS order_year, SUM(amount) FROM Orders GROUP BY customer_id, order_year;

Which index would best improve the performance of this query?

A INDEX(customer_id, order_date)

B INDEX(order_date, customer_id)

C INDEX(customer_id, YEAR(order_date))

D INDEX(order_year, customer_id)

💡 Hint

Indexes on columns used in GROUP BY help, but functions on columns cannot be indexed directly.

You cannot index the result of a function like YEAR(order_date) directly. Indexing customer_id and order_date helps MySQL optimize grouping.

🔧 Debug

advanced

Debugging unexpected GROUP BY results with multiple columns

A developer runs this query:

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

But the result shows only one role per department, not all roles. Why?

A Because the query needs a HAVING clause to filter roles.

B Because COUNT(*) counts only distinct roles, not all employees.

C Because department is not a valid column for grouping.

D Because role is not in the GROUP BY clause, MySQL picks an arbitrary role per department.

💡 Hint

When grouping by fewer columns than selected, non-aggregated columns may show unexpected values.

Selecting columns not in GROUP BY without aggregation causes MySQL to pick an arbitrary value from that group.

🧠 Conceptual

expert

Understanding GROUP BY with multiple columns and NULL values

Consider a table Visits with columns user_id, page, and visit_date. Some page values are NULL. What happens when you run:

SELECT user_id, page, COUNT(*) FROM Visits GROUP BY user_id, page;

How are NULL values in page treated in grouping?

A Each NULL in page is treated as a distinct group, so multiple groups per user_id may have NULL page.

B All rows with NULL in page are grouped together as one group per user_id.

C Rows with NULL page are excluded from the result.

D The query raises an error because NULL cannot be grouped.

💡 Hint

In SQL, NULLs are considered equal for grouping purposes.

GROUP BY treats NULL values as equal, so all NULLs in page for the same user_id form one group.