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

Answer:

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

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