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.

Practice

1. What does the GROUP BY clause do in an SQL query?

easy

A. It deletes duplicate rows from the table.

B. It sorts the rows in ascending order.

C. It groups rows that have the same values in specified columns.

D. It filters rows based on a condition.

Solution

1. Step 1: Understand the purpose of GROUP BY

   The GROUP BY clause is used to group rows that share the same values in one or more columns.

2. Step 2: Differentiate from other clauses

   Sorting is done by ORDER BY, filtering by WHERE, and removing duplicates by DISTINCT, not GROUP BY.

3. Final Answer:

   It groups rows that have the same values in specified columns. -> Option C

4. Quick Check:

   GROUP BY = groups rows by column values [OK]

Hint: GROUP BY groups rows by column values, not sorting or filtering [OK]

Common Mistakes:

• Confusing GROUP BY with ORDER BY
• Thinking GROUP BY filters rows
• Assuming GROUP BY removes duplicates

2. Which of the following SQL queries correctly uses GROUP BY to count employees per department?

easy

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

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

C. SELECT department, COUNT(*) FROM employees WHERE department GROUP BY.;

D. SELECT department, COUNT(*) FROM employees.;

Solution

1. Step 1: Check the syntax of GROUP BY usage

   The correct syntax requires specifying the column after GROUP BY and using aggregate functions properly.

2. Step 2: Analyze each option

   Only SELECT department, COUNT(*) FROM employees GROUP BY department; correctly groups by department and counts employees. The other options have syntax errors: missing column after GROUP BY, no GROUP BY clause, or invalid WHERE syntax.

3. Final Answer:

   SELECT department, COUNT(*) FROM employees GROUP BY department; -> Option B

4. Quick Check:

   GROUP BY column + aggregate function = correct syntax [OK]

Hint: GROUP BY must be followed by column names, aggregate functions used outside [OK]

Common Mistakes:

• Omitting column after GROUP BY
• Using WHERE incorrectly with GROUP BY
• Missing aggregate function with GROUP BY

3. Given the table sales with columns region and amount, what is the result of this query?

SELECT region, SUM(amount) FROM sales GROUP BY region;

medium

A. A list of all sales amounts without grouping.

B. A list of regions with the average sales amount.

C. An error because SUM() cannot be used with GROUP BY.

D. A list of regions with the total sales amount for each region.

Solution

1. Step 1: Understand the query components

   The query groups rows by region and calculates the sum of amount for each group.

2. Step 2: Determine the output

   The output will show each region once with the total sales amount summed up.

3. Final Answer:

   A list of regions with the total sales amount for each region. -> Option D

4. Quick Check:

   GROUP BY region + SUM(amount) = total per region [OK]

Hint: SUM with GROUP BY gives total per group, not average or error [OK]

Common Mistakes:

• Confusing SUM with AVG
• Expecting no grouping effect
• Thinking SUM causes error with GROUP BY

4. Identify the error in this SQL query:

SELECT department, AVG(salary) FROM employees WHERE department GROUP BY department;

medium

A. Missing condition after WHERE clause.

B. AVG() cannot be used with GROUP BY.

C. GROUP BY should come before WHERE.

D. department cannot be selected with AVG().

Solution

1. Step 1: Analyze the WHERE clause

   The WHERE clause requires a condition, but here it only has 'department' which is incomplete and invalid.

2. Step 2: Check GROUP BY and AVG usage

   GROUP BY after WHERE is correct, and AVG() can be used with GROUP BY, so no error there.

3. Final Answer:

   Missing condition after WHERE clause. -> Option A

4. Quick Check:

   WHERE needs a condition, not just a column name [OK]

Hint: WHERE must have a condition; column alone is invalid [OK]

Common Mistakes:

• Using WHERE without condition
• Thinking GROUP BY order is wrong
• Believing AVG() can't be grouped

5. You have a products table with columns category, price, and stock. Which query shows the average price and total stock for each category, but only for categories with more than 10 products?

hard

A. SELECT category, AVG(price), SUM(stock) FROM products GROUP BY category HAVING COUNT(*) > 10;

B. SELECT category, AVG(price), SUM(stock) FROM products WHERE COUNT(*) > 10 GROUP BY category;

C. SELECT category, AVG(price), SUM(stock) FROM products GROUP BY category WHERE COUNT(*) > 10;

D. SELECT category, AVG(price), SUM(stock) FROM products HAVING COUNT(*) > 10 GROUP BY category;

Solution

1. Step 1: Understand filtering groups with HAVING

   To filter groups based on aggregate conditions, use HAVING after GROUP BY.

2. Step 2: Analyze each option's clause order

   Only SELECT category, AVG(price), SUM(stock) FROM products GROUP BY category HAVING COUNT(*) > 10; correctly uses GROUP BY then HAVING. Using WHERE with COUNT(*) is invalid (WHERE processes rows before grouping), and placing HAVING before GROUP BY or incorrect clause ordering is invalid syntax.

3. Final Answer:

   SELECT category, AVG(price), SUM(stock) FROM products GROUP BY category HAVING COUNT(*) > 10; -> Option A

4. Quick Check:

   Use HAVING to filter groups after GROUP BY [OK]

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

Common Mistakes:

• Using WHERE to filter aggregate results
• Placing HAVING before GROUP BY
• Confusing clause order in SQL