Question

Use CTE and create a table that sums the quantity * unit_price for each order.

Answer 1

Answer: To create a table that sums the quantity multiplied by the unit price for each order using Common Table Expressions (CTE), assuming you have a table named "Orders" with columns "OrderID", "Quantity", and "UnitPrice", you can write a SQL query like this:

WITH OrderSums AS (

   SELECT OrderID, SUM(Quantity * UnitPrice) AS TotalOrderPrice

   FROM Orders

   GROUP BY OrderID

)

SELECT *

FROM OrderSums;

Explanation:

• The 'WITH' clause is used to define the CTE named "OrderSums".
• Within the CTE, we select the "OrderID" and calculate the sum of the product of "Quantity" and "UnitPrice" for each order using the 'SUM()' function. We alias this calculated value as "TotalOrderPrice".
• We group the results by "OrderID" using the 'GROUP BY' clause.
• Finally, we select all columns from the "OrderSums" CTE.

This query will generate a table that sums the quantity multiplied by the unit price for each order, with each row showing the OrderID and the total order price.