Question

Write a SELECT statement that uses an aggregate window function to get the total amount of each order. Return these columns:

The order_id column from the Order_Items table

The total amount for each order item in the Order_Items table (Hint: You can calculate the total amount by subtracting the discount amount from the item price and then multiplying it by the quantity)

The total amount for each order Sort the result set in ascending sequence by the order_id column.

Part B.

Modify the solution to exercise 8 so the column that contains the total amount for each order contains a cumulative total by item amount. Add another column to the SELECT statement that uses an aggregate window function to get the average item amount for each order. Modify the SELECT statement so it uses a named window for the two aggregate functions.

Database Schema:
------------------------------
Tables: musicians, orders, order_instruments, instruments, categories

Table columns
-----------------------
musicians: musician_id, email_address, password, first_name, last_name, shipping_address_id, billing_address_id

orders: order_id, musician_id, order_date, ship_amount, tax_amount, ship_date, ship_address_id, card_type, card_number, card_expires, billing_address_id

order_instruments: item_id, order_id, instrument_id, item_price, discount_amount, quantity

instruments: instrument_id, category_id, instrument_code, instrument_name, description, list_price, discount_percent, date_added

categories: category_id, category_name

Answer 1

Solution for Part A(not mentioned in question)

SELECT
orders.order_id AS Order_id,
SUM(order_instruments.quantity *
(order_instruments.item_price -
(instruments.discount_percent * order_instruments.item_price)
/ 100
)
) AS Total_amount_of_each_order
FROM
orders
INNER JOIN order_instruments ON
orders.order_id = order_instruments.order_id
INNER JOIN instruments ON
order_instruments.instrument_id = instruments.instrument_id
GROUP BY
orders.order_id
ORDER BY
orders.order_id ASC;

Solution in image

Solution for Part B

SELECT
orders.order_id AS Order_id,
SUM(order_instruments.quantity *
(order_instruments.item_price -
(instruments.discount_percent * order_instruments.item_price)
/ 100
)
) AS Total_amount_of_each_order,
AVG(order_instruments.item_price) AS Average_item_amount
FROM
orders
INNER JOIN order_instruments ON
orders.order_id = order_instruments.order_id
INNER JOIN instruments ON
order_instruments.instrument_id = instruments.instrument_id
GROUP BY
orders.order_id
ORDER BY
orders.order_id ASC;

Solution in image

Please note :

What is  exercise 8, I don't know but what I understand by question I have wrote query for that.
If you any question, then please do comment. I will try to answer your questions.