I\'m having trouble with the following mySQL problem and code. I\'ve included ta

Question

I'm having trouble with the following mySQL problem and code. I've included table structure also, if that helps.

6. Write a SELECT statement that returns one row for each customer that has orders with these

columns:

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.

customers v Columns customer id email address password first name ast name shipping address id billing address id v P Indexes PRIMARY email address Foreign Keys Triggers

Explanation / Answer

THE above query you gave

SELECT email_address, COUNT(o.order_id) as number_of_orders, sum((item_price-discount_amount)*quantity) As Total
FROM Customers c JOIN Orders o
ON c.customer_id = o.customer_id
JOIN (
SELECT order_id,
CASE WHEN item_price < 400 THEN 0 ELSE item_price END FROM order_items ) oi on oi.order_id = o.order_id
GROUP BY email_address
HAVING COUNT (o.order_id) > 1
ORDER BY number_of_orders DESC;

or use the below query

SELECT EmailAddress, COUNT(o.OrderID) AS OrderCount,
SUM((ItemPrice - DiscountAmount) * Quantity) AS OrderTotal
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderItems oi ON o.OrderID = oi.OrderID
GROUP BY EmailAddress
HAVING COUNT(o.OrderID) > 1
ORDER BY OrderTotal DESC

This is should work.

Likewise, you should to dependably utilize table aliases names while addressing to a coulmn-regardless of the possibility that there are no name conflicted/clashed.

It enhances readability and keep you from committing little errors.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.