GROUP BY single column in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of GROUP BY on a single column

Given the table Sales with columns Product and Quantity, what is the output of the following query?

SELECT Product, SUM(Quantity) FROM Sales GROUP BY Product;

SQL

CREATE TABLE Sales (Product VARCHAR(20), Quantity INT);
INSERT INTO Sales VALUES ('Apple', 10), ('Banana', 5), ('Apple', 15), ('Banana', 10), ('Cherry', 7);

A [{"Product": "Apple", "SUM(Quantity)": 25}, {"Product": "Banana", "SUM(Quantity)": 15}, {"Product": "Cherry", "SUM(Quantity)": 7}]

B [{"Product": "Apple", "SUM(Quantity)": 10}, {"Product": "Banana", "SUM(Quantity)": 5}, {"Product": "Apple", "SUM(Quantity)": 15}, {"Product": "Banana", "SUM(Quantity)": 10}, {"Product": "Cherry", "SUM(Quantity)": 7}]

C [{"Product": "Apple", "SUM(Quantity)": 25}, {"Product": "Banana", "SUM(Quantity)": 15}]

D [{"Product": "Apple", "SUM(Quantity)": 10}, {"Product": "Banana", "SUM(Quantity)": 15}, {"Product": "Cherry", "SUM(Quantity)": 7}]

💡 Hint

GROUP BY combines rows with the same value in the grouped column and aggregates other columns.

The query groups rows by Product and sums the Quantity for each product. Apple has 10 + 15 = 25, Banana has 5 + 10 = 15, Cherry has 7.

🧠 Conceptual

intermediate

Understanding GROUP BY behavior

What happens if you run this query?

SELECT Product, Quantity FROM Sales GROUP BY Product;

Assuming the same Sales table as before.

A It returns one row per product with a random Quantity value from that product's rows.

B It returns an error because Quantity is not aggregated or in GROUP BY.

C It sums the Quantity for each product automatically.

D It returns all rows without grouping.

💡 Hint

In SQL, columns in SELECT must be aggregated or included in GROUP BY.

SQL requires that all selected columns be either grouped or aggregated. Quantity is neither, so this query causes an error.

📝 Syntax

advanced

Identify the syntax error in GROUP BY query

Which option contains a syntax error when grouping by a single column?

SQL

Table: Orders(OrderID INT, Customer VARCHAR(20), Amount INT)

A SELECT Customer, SUM(Amount) FROM Orders GROUP BY Customer;

B SELECT Customer, SUM(Amount) FROM Orders GROUP BY Customer ORDER BY Customer;

C SELECT Customer, SUM(Amount) FROM Orders GROUP Customer;

D SELECT Customer, SUM(Amount) FROM Orders GROUP BY Customer HAVING SUM(Amount) > 100;

💡 Hint

Check the syntax of the GROUP BY clause.

Option C is missing the keyword BY after GROUP, causing a syntax error.

❓ optimization

advanced

Optimizing GROUP BY query performance

You have a large table Transactions with columns UserID, Amount, and Date. You want to get total amount per user quickly. Which option improves query speed the most?

A Add an index on UserID and run: SELECT UserID, SUM(Amount) FROM Transactions GROUP BY UserID;

B Add an index on Amount and run: SELECT UserID, SUM(Amount) FROM Transactions GROUP BY UserID;

C Run: SELECT UserID, SUM(Amount) FROM Transactions;

D Run: SELECT UserID, SUM(Amount) FROM Transactions GROUP BY UserID ORDER BY UserID;

💡 Hint

Indexes help speed up grouping on the indexed column.

Indexing UserID helps the database quickly group rows by UserID, improving performance.

🔧 Debug

expert

Why does this GROUP BY query return fewer rows than expected?

Given the table Employees with columns Department and Salary, you run:

SELECT Department, COUNT(*) FROM Employees GROUP BY Department;

You notice some departments are missing in the result. What is the most likely reason?

A GROUP BY only shows departments with the highest salary.

B The COUNT(*) function filters out departments with zero employees.

C The query needs a WHERE clause to include all departments.

D Some departments have NULL values in Department column, and GROUP BY excludes NULL groups.

💡 Hint

Think about how NULL values behave in GROUP BY.

GROUP BY treats NULL as a group, but some databases may exclude NULL groups or they appear as NULL. Departments with NULL values might be missing or grouped separately.