GROUP BY with aggregate functions in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of GROUP BY with COUNT

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

SELECT product_id, COUNT(*) AS total_sales
FROM Sales
GROUP BY product_id;

SQL

CREATE TABLE Sales (product_id INT, quantity INT);
INSERT INTO Sales VALUES (1, 10), (2, 5), (1, 7), (3, 2), (2, 3);

A [{"product_id":1,"total_sales":2},{"product_id":2,"total_sales":2},{"product_id":3,"total_sales":1}]

B [{"product_id":1,"total_sales":17},{"product_id":2,"total_sales":8},{"product_id":3,"total_sales":2}]

C [{"product_id":1,"total_sales":1},{"product_id":2,"total_sales":1},{"product_id":3,"total_sales":1}]

D [{"product_id":1,"total_sales":3},{"product_id":2,"total_sales":2},{"product_id":3,"total_sales":1}]

💡 Hint

COUNT(*) counts rows per group, not sum of quantities.

The query groups rows by product_id and counts how many rows each product has. Product 1 appears twice, product 2 twice, product 3 once.

❓ query_result

intermediate

SUM with GROUP BY output

Consider the table Orders with columns customer_id and order_amount. What is the output of this query?

SELECT customer_id, SUM(order_amount) AS total_spent
FROM Orders
GROUP BY customer_id;

SQL

CREATE TABLE Orders (customer_id INT, order_amount DECIMAL);
INSERT INTO Orders VALUES (101, 50.5), (102, 20.0), (101, 30.0), (103, 15.0), (102, 25.0);

A [{"customer_id":101,"total_spent":2},{"customer_id":102,"total_spent":2},{"customer_id":103,"total_spent":1}]

B [{"customer_id":101,"total_spent":50.5},{"customer_id":102,"total_spent":20.0},{"customer_id":103,"total_spent":15.0}]

C [{"customer_id":101,"total_spent":80.5},{"customer_id":102,"total_spent":45.0},{"customer_id":103,"total_spent":15.0}]

D [{"customer_id":101,"total_spent":30.0},{"customer_id":102,"total_spent":25.0},{"customer_id":103,"total_spent":15.0}]

💡 Hint

SUM adds all order_amount values per customer.

The query sums order_amount for each customer_id. Customer 101 has 50.5 + 30.0 = 80.5, customer 102 has 20.0 + 25.0 = 45.0, customer 103 has 15.0.

📝 Syntax

advanced

Identify the syntax error in GROUP BY query

Which option contains a syntax error in this GROUP BY query?

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

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

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

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

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

💡 Hint

GROUP BY must be followed by column names.

Option A ends GROUP BY without specifying columns, causing syntax error. Others are valid SQL.

❓ optimization

advanced

Optimizing GROUP BY with indexes

You have a large Transactions table with columns user_id, amount, and date. Which option best improves performance of this query?

SELECT user_id, SUM(amount) FROM Transactions WHERE date >= '2024-01-01' GROUP BY user_id;

A Create an index on (user_id)

B Create an index on (amount)

C Create an index on (user_id, date)

D Create an index on (date, user_id)

💡 Hint

Indexes help filter rows before grouping.

Index on (date, user_id) helps filter by date first, then group by user_id efficiently.

🧠 Conceptual

expert

Understanding HAVING vs WHERE with GROUP BY

Which statement correctly explains the difference between WHERE and HAVING clauses in a GROUP BY query?

A WHERE and HAVING both filter groups after aggregation but WHERE is faster.

B WHERE filters rows before grouping; HAVING filters groups after aggregation.

C WHERE and HAVING both filter rows before grouping but HAVING is faster.

D WHERE filters groups after aggregation; HAVING filters rows before grouping.

💡 Hint

Think about when filtering happens in the query process.

WHERE filters individual rows before grouping; HAVING filters groups after aggregation functions are applied.