6. Write a SELECT statement that returns one row for each customer that has orde

Question

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

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;

Related Questions

Navigate

• Browse (All)
• Browse 6
• Subjects

---