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.

Answer 1

Answer alongwith Explanation:

The following is the statement that uses an aggregate window function in order to get the total amount of each order.

SELECT isnull(p1.PName,'Total') As PName,

              Sum((t1.Price minus  t1.Discount ) multiply with Quantity) AS TotalAmount

FROM     Products AS t1

              JOIN OrderItems AS t2

              ON t1.ProductID = t2.ProductID

GROUP  BY Rollup(PName) ORDER BY order id ASC

Thus, it can be used for the function.