Question

Write a SELECT statement that returns one row for each customer that has orders with these columns: The email_address column 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

Answer 1

Answer:

The statement is as follows:

Explanation:

We had better avoid such correlated subqueries by instead using aggregation with GROUP BY:

SELECT

   c.email_address,

   COUNT(DISTINCT o.order_id) AS num_orders,

   COALESCE(SUM(oi.quantity * (oi.item_price - oi.discount_amount)), 0) AS total_amount

FROM customers c

LEFT JOIN orders o

   ON c.customer_id = o.customer_id

INNER JOIN order_items oi

   ON o.order_id = oi.order_id

GROUP BY

   c.customer_id,

   c.email_address;