Please help! I am stuck on this question--the text is very dry and the homework
ID: 3551358 • Letter: P
Question
Please help! I am stuck on this question--the text is very dry and the homework questions don't correlate to the chapters. I wish the chapters focussed more on a step-by-step so you could learn to actually build the queries!
4. 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 customers email address and the largest order for that
customer. To do this, you can group the result set by the EmailAddress column.
Explanation / Answer
SQL> SELECT EmailAddress, OrderID, count (*) as OrderTotal
FROM OrderItems GROUP BY EmailAddress, OrderID;
SQL> SELECT EmailAddress, MAX(OrderID)
FROM (SELECT EmailAddress, OrderID, count(*) as OrderTotal FROM OrderItems GROUP BY EmailAddress, OrderID) GROUP BY EmailAddress;