GROUP BY clause in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of GROUP BY with aggregate function

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

SELECT product, SUM(quantity) FROM Sales GROUP BY product;

MySQL

CREATE TABLE Sales (product VARCHAR(20), quantity INT);
INSERT INTO Sales VALUES ('apple', 10), ('banana', 5), ('apple', 15), ('banana', 10), ('orange', 7);

A [{"product": "apple", "SUM(quantity)": 25}, {"product": "banana", "SUM(quantity)": 15}, {"product": "orange", "SUM(quantity)": 7}]

B [{"product": "apple", "SUM(quantity)": 10}, {"product": "banana", "SUM(quantity)": 5}, {"product": "orange", "SUM(quantity)": 7}]

C [{"product": "apple", "SUM(quantity)": 25}, {"product": "banana", "SUM(quantity)": 15}]

D [{"product": "apple", "SUM(quantity)": 25}, {"product": "banana", "SUM(quantity)": 15}, {"product": "orange", "SUM(quantity)": 0}]

💡 Hint

GROUP BY groups rows by product and SUM adds quantities per group.

The query groups rows by product and sums the quantity for each product. Apple has 10+15=25, banana 5+10=15, orange 7.

🧠 Conceptual

intermediate

Understanding GROUP BY with multiple columns

Consider a table Orders with columns customer_id, product, and quantity. What does this query do?

SELECT customer_id, product, COUNT(*) FROM Orders GROUP BY customer_id, product;

A Counts total orders for each product regardless of customer.

B Counts total orders for each customer regardless of product.

C Counts how many orders each customer made for each product.

D Counts total orders in the table without grouping.

💡 Hint

GROUP BY with two columns groups rows by unique pairs of those columns.

The query groups rows by both customer_id and product, so it counts how many orders each customer made for each product separately.

📝 Syntax

advanced

Identify the syntax error in GROUP BY usage

Which option contains a syntax error in the GROUP BY clause?

SELECT department, AVG(salary) FROM Employees GROUP BY;

A SELECT department, AVG(salary) FROM Employees GROUP BY department;

B SELECT department, AVG(salary) FROM Employees GROUP BY;

C SELECT department, AVG(salary) FROM Employees GROUP BY department ORDER BY salary;

D SELECT department, AVG(salary) FROM Employees GROUP BY department, salary;

💡 Hint

GROUP BY must specify at least one column to group by.

Option B has GROUP BY with no columns, which is a syntax error. GROUP BY requires at least one column name.

❓ optimization

advanced

Optimizing GROUP BY with indexes

Which index will best improve performance of this query?

SELECT city, COUNT(*) FROM Customers GROUP BY city;

A CREATE INDEX idx_city ON Customers(city);

B CREATE INDEX idx_name ON Customers(name);

C CREATE INDEX idx_city_name ON Customers(city, name);

D CREATE INDEX idx_id ON Customers(id);

💡 Hint

Indexes on columns used in GROUP BY help speed grouping.

An index on city helps the database quickly group rows by city. Indexes on other columns do not help this query.

🔧 Debug

expert

Why does this GROUP BY query fail?

Given the table Employees with columns id, department, and salary, why does this query cause an error?

SELECT id, department, AVG(salary) FROM Employees GROUP BY department;

A Because AVG(salary) cannot be used with GROUP BY.

B Because GROUP BY must include all columns in the table.

C Because department is not in SELECT clause.

D Because id is not in GROUP BY or an aggregate function.

💡 Hint

Columns in SELECT must be in GROUP BY or aggregated.

The query selects id which is neither aggregated nor in GROUP BY, causing an error in MySQL strict mode.