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.

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 limits the number of rows returned.

Solution

1. Step 1: Understand the purpose of GROUP BY

   The GROUP BY clause collects rows with the same values in specified columns into summary rows.

2. Step 2: Differentiate from other clauses

   ORDER BY sorts rows, DELETE removes rows, and LIMIT restricts output count, which are different from grouping.

3. Final Answer:

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

4. Quick Check:

   GROUP BY = grouping rows [OK]

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

Common Mistakes:

• Confusing GROUP BY with ORDER BY
• Thinking GROUP BY deletes duplicates
• Assuming GROUP BY limits rows

2. Which of the following SQL queries correctly groups sales by product and orders the result by total sales descending?

easy

A. SELECT product, SUM(amount) FROM sales GROUP BY product ORDER BY SUM(amount) DESC;

B. SELECT product, SUM(amount) FROM sales ORDER BY product GROUP BY SUM(amount) DESC;

C. SELECT product, SUM(amount) FROM sales GROUP BY product ORDER BY product DESC;

D. SELECT product, SUM(amount) FROM sales ORDER BY SUM(amount) GROUP BY product DESC;

Solution

1. Step 1: Check GROUP BY and ORDER BY order

   The correct syntax is GROUP BY first, then ORDER BY to sort grouped results.

2. Step 2: Verify ordering by aggregated column

   Ordering by SUM(amount) DESC sorts by total sales descending, matching the requirement.

3. Final Answer:

   SELECT product, SUM(amount) FROM sales GROUP BY product ORDER BY SUM(amount) DESC; -> Option A

4. Quick Check:

   GROUP BY then ORDER BY with aggregate [OK]

Hint: GROUP BY before ORDER BY; order by aggregate for totals [OK]

Common Mistakes:

• Swapping GROUP BY and ORDER BY order
• Ordering by non-aggregated columns incorrectly
• Using ORDER BY before GROUP BY

3. Given the table orders with columns customer_id and order_total, what will this query return?

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id ORDER BY order_count ASC;

medium

A. Syntax error due to incorrect ORDER BY usage.

B. List of customers with their order counts sorted from highest to lowest.

C. List of customers with total order amounts sorted ascending.

D. List of customers with their order counts sorted from lowest to highest.

Solution

1. Step 1: Understand the GROUP BY and COUNT

   The query groups rows by customer_id and counts orders per customer.

2. Step 2: Analyze ORDER BY order_count ASC

   Ordering by order_count ascending sorts customers from fewest to most orders.

3. Final Answer:

   List of customers with their order counts sorted from lowest to highest. -> Option D

4. Quick Check:

   ORDER BY ASC sorts ascending [OK]

Hint: ORDER BY ASC sorts from smallest to largest [OK]

Common Mistakes:

• Assuming ORDER BY ASC sorts descending
• Confusing COUNT with SUM
• Thinking query returns total order amounts

4. Identify the error in this SQL query:

SELECT department, COUNT(*) FROM employees ORDER BY COUNT(*) DESC GROUP BY department;

medium

A. GROUP BY must come before ORDER BY in the query.

B. COUNT(*) cannot be used in ORDER BY clause.

C. Missing alias for COUNT(*) causes syntax error.

D. ORDER BY cannot sort aggregated columns.

Solution

1. Step 1: Check SQL clause order

   GROUP BY must appear before ORDER BY in SQL syntax.

2. Step 2: Identify the error in clause sequence

   The query places ORDER BY before GROUP BY, causing syntax error.

3. Final Answer:

   GROUP BY must come before ORDER BY in the query. -> Option A

4. Quick Check:

   GROUP BY before ORDER BY [OK]

Hint: GROUP BY always before ORDER BY in SQL [OK]

Common Mistakes:

• Placing ORDER BY before GROUP BY
• Assuming COUNT(*) can't be in ORDER BY
• Forgetting clause order rules

5. You have a sales table with columns region, salesperson, and amount. You want to find the total sales per region, but only show regions with total sales above 1000, sorted by total sales descending. Which query achieves this?

hard

A. SELECT region, SUM(amount) FROM sales WHERE SUM(amount) > 1000 GROUP BY region ORDER BY SUM(amount) DESC;

B. SELECT region, SUM(amount) FROM sales GROUP BY region HAVING SUM(amount) > 1000 ORDER BY SUM(amount) DESC;

C. SELECT region, SUM(amount) FROM sales GROUP BY region ORDER BY SUM(amount) DESC HAVING SUM(amount) > 1000;

D. SELECT region, SUM(amount) FROM sales ORDER BY SUM(amount) DESC HAVING SUM(amount) > 1000 GROUP BY region;

Solution

1. Step 1: Use GROUP BY to group sales by region

   Grouping by region allows aggregation of sales per region.

2. Step 2: Filter groups with HAVING clause

   HAVING filters groups after aggregation; WHERE cannot filter on aggregates.

3. Step 3: Sort results with ORDER BY descending

   ORDER BY SUM(amount) DESC sorts regions by total sales from highest to lowest.

4. Final Answer:

   SELECT region, SUM(amount) FROM sales GROUP BY region HAVING SUM(amount) > 1000 ORDER BY SUM(amount) DESC; -> Option B

5. Quick Check:

   GROUP BY + HAVING + ORDER BY correct order [OK]

Hint: Use HAVING to filter grouped results, ORDER BY last [OK]

Common Mistakes:

• Using WHERE to filter aggregated results
• Placing HAVING after ORDER BY
• Incorrect clause order