Question

Write a SELECT statement that returns three columns: EmailAddress, OrderID, and the order total for each customer. To do this, you can group the result set by the EmailAddress and OrderID columns. In addition, you must calculate the order total from the columns in the OrderItems table.Write a second SELECT statement that uses the first SELECT statement in its FROM clause. The main query should return two columns: the customer’s email address and the largest order for that customer. To do this, you can group the result set by the EmailAddress column.

Answer 1

First SELECT Statement:

sql

SELECT c.EmailAddress, oi.OrderID, SUM(oi.Price * oi.Quantity) AS OrderTotal

FROM Customers c

JOIN Orders o ON c.CustomerID = o.CustomerID

JOIN OrderItems oi ON o.OrderID = oi.OrderID

GROUP BY c.EmailAddress, oi.OrderID;

This statement first joins the `Customers`, `Orders`, and `OrderItems` tables on their respective foreign key relationships. Then, it calculates the order total by multiplying the price and quantity for each item within an order and summing those values using the `SUM` aggregate function.

Second SELECT Statement:

sql

SELECT EmailAddress, MAX(OrderTotal) AS LargestOrder

FROM (

SELECT c.EmailAddress, oi.OrderID, SUM(oi.Price * oi.Quantity) AS OrderTotal

FROM Customers c

JOIN Orders o ON c.CustomerID = o.CustomerID

JOIN OrderItems oi ON o.OrderID = oi.OrderID

GROUP BY c.EmailAddress, oi.OrderID

) AS subquery

GROUP BY EmailAddress;

This statement uses the first SELECT statement as a subquery within its FROM clause. This allows us to pre-calculate the order total for each customer and order combination.

Answer 2

Select EmailAddress,max(total) from (Select EmailAddress, OrderID, sum(total) as total from OrderItems group by EmailAddress, OrderID) group by EmailAddress

Step-by-step explanation:

First step is group the rows by email and order id and sum de total of orders

Select EmailAddress, OrderID, sum(total) as total from OrderItems group by EmailAddress, OrderID

Then you use the above query as a subquery grouping by the email and selecting the max value by client

Select EmailAddress,max(total) from (Select EmailAddress, OrderID, sum(total) as total from OrderItems group by EmailAddress, OrderID) group by EmailAddress