JOIN with aggregate functions in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate total sales per customer

Given two tables Customers and Orders, what is the total amount spent by each customer? The Orders table has a column amount representing the order value. Use a JOIN and aggregate function to find the total spent per customer.

MySQL

SELECT c.customer_id, c.customer_name, SUM(o.amount) AS total_spent
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;

A [{"customer_id": 1, "customer_name": "Alice", "total_spent": 100}, {"customer_id": 2, "customer_name": "Bob", "total_spent": 150}]

B [{"customer_id": 1, "customer_name": "Alice", "total_spent": 400}, {"customer_id": 2, "customer_name": "Bob", "total_spent": 150}]

C [{"customer_id": 1, "customer_name": "Alice", "total_spent": 250}, {"customer_id": 2, "customer_name": "Bob", "total_spent": null}]

D [{"customer_id": 1, "customer_name": "Alice", "total_spent": 250}, {"customer_id": 2, "customer_name": "Bob", "total_spent": 150}]

💡 Hint

Use JOIN to connect customers with their orders, then SUM the order amounts grouped by customer.

The query joins Customers and Orders on customer_id, then sums the amounts per customer. Alice has orders totaling 250, Bob has 150.

❓ query_result

intermediate

Find the number of orders per product

Using tables Products and OrderDetails, find how many orders include each product. Use JOIN and COUNT aggregate function.

MySQL

SELECT p.product_id, p.product_name, COUNT(od.order_id) AS order_count
FROM Products p
JOIN OrderDetails od ON p.product_id = od.product_id
GROUP BY p.product_id, p.product_name;

A [{"product_id": 101, "product_name": "Pen", "order_count": 3}, {"product_id": 102, "product_name": "Notebook", "order_count": 2}]

B [{"product_id": 101, "product_name": "Pen", "order_count": 2}, {"product_id": 102, "product_name": "Notebook", "order_count": 2}]

C [{"product_id": 101, "product_name": "Pen", "order_count": 3}, {"product_id": 102, "product_name": "Notebook", "order_count": 0}]

D [{"product_id": 101, "product_name": "Pen", "order_count": null}, {"product_id": 102, "product_name": "Notebook", "order_count": 2}]

💡 Hint

Count the order IDs for each product after joining the tables.

The JOIN connects products with order details. Counting order_id per product shows how many orders include that product.

📝 Syntax

advanced

Identify the syntax error in JOIN with aggregate

Which option contains a syntax error in this query that joins Employees and Sales and calculates total sales per employee?

MySQL

SELECT e.employee_id, e.name, SUM(s.amount) AS total_sales
FROM Employees e
JOIN Sales s ON e.employee_id = s.employee_id
GROUP BY e.employee_id, e.name;

A SELECT e.employee_id, e.name, SUM(s.amount) AS total_sales FROM Employees e JOIN Sales s ON e.employee_id = s.employee_id GROUP BY e.employee_id, s.amount;

B SELECT e.employee_id, e.name, SUM(s.amount) AS total_sales FROM Employees e JOIN Sales s ON e.employee_id = s.employee_id GROUP BY e.employee_id;

C SELECT e.employee_id, e.name, SUM(s.amount) AS total_sales FROM Employees e JOIN Sales s ON e.employee_id = s.employee_id GROUP BY e.name, e.employee_id;

D SELECT e.employee_id, e.name, SUM(s.amount) AS total_sales FROM Employees e JOIN Sales s ON e.employee_id = s.employee_id GROUP BY e.employee_id, e.name;

💡 Hint

GROUP BY must include all non-aggregated selected columns from the first table.

Option A groups by s.amount which is aggregated, causing a syntax error. GROUP BY should only include e.employee_id and e.name.

❓ optimization

advanced

Optimize query with JOIN and aggregate

Which query is the most efficient to find the average order amount per customer from Customers and Orders tables?

A SELECT c.customer_id, AVG(o.amount) AS avg_amount FROM Customers c JOIN Orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id;

B SELECT customer_id, AVG(amount) AS avg_amount FROM Orders GROUP BY customer_id;

C SELECT c.customer_id, (SELECT AVG(amount) FROM Orders WHERE customer_id = c.customer_id) AS avg_amount FROM Customers c;

D SELECT c.customer_id, SUM(o.amount)/COUNT(o.order_id) AS avg_amount FROM Customers c JOIN Orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id;

💡 Hint

Avoid unnecessary JOINs when data is only in one table.

Option B queries only Orders and groups by customer_id, which is faster than joining with Customers if only customer_id and average amount are needed.

🧠 Conceptual

expert

Understanding JOIN with aggregate and NULL values

Consider Customers and Orders tables. Some customers have no orders. Which query correctly lists all customers with their total order amount, showing 0 for customers without orders?

A SELECT c.customer_id, IFNULL(SUM(o.amount), 0) AS total_spent FROM Customers c RIGHT JOIN Orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id;

B SELECT c.customer_id, SUM(o.amount) AS total_spent FROM Customers c JOIN Orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id;

C SELECT c.customer_id, COALESCE(SUM(o.amount), 0) AS total_spent FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id;

D SELECT c.customer_id, SUM(o.amount) AS total_spent FROM Customers c LEFT JOIN Orders o ON c.customer_id = o.customer_id WHERE o.amount IS NOT NULL GROUP BY c.customer_id;

💡 Hint

Use LEFT JOIN to include all customers and COALESCE to replace NULL sums with zero.

LEFT JOIN includes all customers even without orders. SUM returns NULL if no matching rows, so COALESCE replaces NULL with 0.