Here is the code I have, but need help figuring where it\'s going wrong. Query i
ID: 3736069 • Letter: H
Question
Here is the code I have, but need help figuring where it's going wrong. Query instructions are listed below:
SELECT email_address, COUNT(DISTINCT oi.order_id) AS num_orders, SUM((item_price - discount_amount) * quantity) AS total_amount
FROM customers c JOIN orders o
ON c.customer_id = o.customer_id
JOIN order_items oi
ON oi.order_id = o.order_id
GROUP BY email_address
HAVING num_orders > 1
ORDER BY total_amount DESC;
Here is the query:
Write a SELECT statement that returns one row for each customer that has orders
with these columns: (3 rows)
The email_address from the Customers table
A count of the number of orders
The total amount for each order (Hint: First, subtract the discount amount from the price. Then, multiply by the quantity.)
Return only those rows where the customer has more than 1 order.
Sort the result set in descending sequence by the sum of the line item amounts.
Explanation / Answer
SELECT email_address, count(oi.order_id) AS num_orders, (SUM(item_price) - sum(discount_amount) ) * sum(quantity) AS total_amount FROM customers c JOIN orders o ON c.customer_id = o.customer_id JOIN order_items oi ON oi.order_id = o.order_id GROUP BY email_address HAVING count(oi.order_id) > 1 order by sum(item_price) desc