I am using SQL, myguitarshop Write a SELECT statement that returns three columns

Question

I am using SQL, myguitarshop

Write a SELECT statement that returns three columns:: email_address,, order_id,, and the order total for each customer.. To do this,, you can group the result set by the email_address and order_id columns.. In a ddition,, you must calculate the order total from the columns in the Order_Items 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 l argest order for that customer.. To do this,, you can group the result set by the email_address.

This is what I got for the first part of the question, but I am confused as to what the second part of the quesiton means (the second select statement).

SELECT c.email_address, o.order_id, SUM((oi.item_price - oi.discount_amount) * oi.quantity) AS order_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.email_address, o.order_id
ORDER BY c.email_address, o.order_id;

Explanation / Answer

Answer:

In the below query, the first select will be passed in the from clause so the outer/ second select will use the result generated from the inner select.

SELECT res.email_address, MAX(res.order_total) from (SELECT c.email_address, o.order_id, SUM((oi.item_price - oi.discount_amount) * oi.quantity) AS order_total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.email_address, o.order_id) res;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.