GROUP BY with ORDER BY in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of GROUP BY with ORDER BY on aggregated data

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

SELECT product, SUM(quantity) AS total_quantity
FROM Sales
GROUP BY product
ORDER BY total_quantity DESC;

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", "total_quantity": 25}, {"product": "Banana", "total_quantity": 15}, {"product": "Cherry", "total_quantity": 7}]

B [{"product": "Banana", "total_quantity": 15}, {"product": "Apple", "total_quantity": 25}, {"product": "Cherry", "total_quantity": 7}]

C [{"product": "Cherry", "total_quantity": 7}, {"product": "Banana", "total_quantity": 15}, {"product": "Apple", "total_quantity": 25}]

D [{"product": "Apple", "total_quantity": 25}, {"product": "Cherry", "total_quantity": 7}, {"product": "Banana", "total_quantity": 15}]

💡 Hint

Remember that ORDER BY sorts the results after grouping and aggregation.

The query groups rows by product, sums the quantity for each product, then orders the results by the summed quantity in descending order. Apple has 25, Banana 15, Cherry 7, so the order is Apple, Banana, Cherry.

❓ query_result

intermediate

Effect of ORDER BY on GROUP BY with multiple columns

Consider the table Orders with columns customer, product, and amount. What is the output of this query?

SELECT customer, product, SUM(amount) AS total_amount
FROM Orders
GROUP BY customer, product
ORDER BY customer ASC, total_amount DESC;

SQL

CREATE TABLE Orders (customer VARCHAR(20), product VARCHAR(20), amount INT);
INSERT INTO Orders VALUES ('Alice', 'Pen', 10), ('Alice', 'Pen', 5), ('Alice', 'Notebook', 7), ('Bob', 'Pen', 3), ('Bob', 'Notebook', 8);

A [{"customer": "Bob", "product": "Notebook", "total_amount": 8}, {"customer": "Bob", "product": "Pen", "total_amount": 3}, {"customer": "Alice", "product": "Pen", "total_amount": 15}, {"customer": "Alice", "product": "Notebook", "total_amount": 7}]

B [{"customer": "Alice", "product": "Pen", "total_amount": 15}, {"customer": "Alice", "product": "Notebook", "total_amount": 7}, {"customer": "Bob", "product": "Pen", "total_amount": 3}, {"customer": "Bob", "product": "Notebook", "total_amount": 8}]

C [{"customer": "Alice", "product": "Notebook", "total_amount": 7}, {"customer": "Alice", "product": "Pen", "total_amount": 15}, {"customer": "Bob", "product": "Notebook", "total_amount": 8}, {"customer": "Bob", "product": "Pen", "total_amount": 3}]

D [{"customer": "Alice", "product": "Pen", "total_amount": 15}, {"customer": "Alice", "product": "Notebook", "total_amount": 7}, {"customer": "Bob", "product": "Notebook", "total_amount": 8}, {"customer": "Bob", "product": "Pen", "total_amount": 3}]

💡 Hint

ORDER BY sorts first by customer ascending, then by total_amount descending within each customer.

The query groups by customer and product, sums amounts, then orders by customer ascending and total_amount descending. For Alice, Pen (15) comes before Notebook (7). For Bob, Notebook (8) comes before Pen (3).

📝 Syntax

advanced

Identify the syntax error in GROUP BY with ORDER BY

Which option contains a syntax error in this SQL query?

SELECT department, COUNT(*) AS emp_count
FROM Employees
GROUP BY department
ORDER BY emp_count DESC;

A SELECT department, COUNT(*) AS emp_count FROM Employees GROUP BY department ORDER BY emp_count DESC;

B SELECT department, COUNT(*) AS emp_count FROM Employees GROUP BY department ORDER BY COUNT(*) DESC;

C SELECT department, COUNT(*) AS emp_count FROM Employees GROUP BY department ORDER BY department DESC;

D SELECT department, COUNT(*) AS emp_count FROM Employees GROUP BY department ORDER BY emp_count;

💡 Hint

ORDER BY cannot use aggregate functions directly unless in SELECT or GROUP BY.

Option B tries to ORDER BY COUNT(*) directly, which is not allowed in standard SQL. The alias emp_count must be used instead.

❓ optimization

advanced

Optimizing GROUP BY with ORDER BY on large data

You have a large table Transactions with columns category and amount. Which query is the most efficient to get total amount per category ordered by total amount descending?

A SELECT category, SUM(amount) AS total_amount FROM Transactions GROUP BY category;

B SELECT category, SUM(amount) AS total_amount FROM Transactions ORDER BY total_amount DESC GROUP BY category;

C SELECT category, SUM(amount) AS total_amount FROM Transactions GROUP BY category ORDER BY total_amount DESC;

D SELECT category, SUM(amount) AS total_amount FROM Transactions ORDER BY SUM(amount) DESC;

💡 Hint

GROUP BY must come before ORDER BY in SQL syntax.

Option C correctly groups by category, sums amount, then orders by total_amount descending. Option C has ORDER BY before GROUP BY which is invalid syntax. Option C lacks ordering. Option C tries to ORDER BY aggregate without GROUP BY which is invalid.

🧠 Conceptual

expert

Understanding ORDER BY with GROUP BY and NULL values

Given a table Employees with columns team and salary, some team values are NULL. What is the output order of this query?

SELECT team, AVG(salary) AS avg_salary
FROM Employees
GROUP BY team
ORDER BY avg_salary ASC;

Assuming teams with NULL are grouped together, how does ORDER BY handle NULL avg_salary values?

A Rows with NULL avg_salary appear last in ascending order.

B Rows with NULL avg_salary appear first in ascending order.

C Rows with NULL avg_salary are excluded from the result.

D Rows with NULL avg_salary appear randomly in the order.

💡 Hint

By default, NULLs sort last in ascending order in most SQL databases.

When ordering ascending, NULL values appear last by default. They are included in the result but sorted after all non-NULL values.