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

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 email_address,

SUM(item_price * Quantity) AS item_price_total,

SUM(discount_amount * Quantity) AS discount_amount_total

FROM Customers c

JOIN Orders o ON c.CustomerID = o.CustomerID

JOIN Order_Items oi ON o.OrderID = oi.OrderID

GROUP BY email_address

ORDER BY item_price_total DESC

Explanation:

In this SQL statement the SELECT statement selects the following columns:

email_address

item_price

Quantity

discount_amount

There are two tables Customers and Order_Items

SUM aggregate function is used to add the values of the product of the columns discount_amount and Quantity. It is also used to get the sum of the product of two columns discount_amount and Quantity.

A new temporary column named item_price_total is used to name the sum of the product of two columns discount_amount and Quantity using AS which is ALIAS and it is used to give a name to some columns or a table.

Similarly discount_amount_total name is given to the column which calculate the sum of the product of two columns i.e. discount_amount and Quantity.

JOIN is used here to join the columns from the tables Order_items and Customers.

GROUP BY is used to group the result of rows and is used with functions like SUM. Here the rows are grouped by the email address.

ORDER BY is used to order the result. Here the result is ordered by item_price_total in descending order.

This SELECT statement can also be written as following:

SELECT c.email_address,

SUM(o.item_price * o.Quantity),

SUM(o.discount_amount * o.quantity)

FROM customer c

JOIN Order_Items o ON o.id = c.id

GROUP BY c.email_address