Question

in mysql

3. 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  

The sum of the item price in the Order_Items table multiplied by the  

quantity in the Order_Items table  

The sum of the discount amount column in the Order_Items table  

multiplied by the quantity in the Order_Items table  

Sort the result set in descending sequence by the item price total for each customer.  

Answer 1

SELECT c.email_address,SUM(o.item_price * o.quantity), SUM(o.discount_amount * o.quantity) FROM Customers c, Order_Items o WHERE c.Id = o.Id GROUP BY c.email_address ORDER BY o.item_price DESC;

The above statement will return one row for each customer. For join we need a common column from both the table. I assumed "Id" column is common between both the table as you didn't mention about it in your question. You should write the name of the column which is common between two table if not "Id".