Question

Using the CTE named customer_information that you have in the given code below. Add a correlated subquery that also shows the total value of the order. Using an alias, name this calculated field OrderTotal. Because this is a corelated subquery, it falls into the category of aggregate and does not need to be included in the GROUP BY clause. (Hint: You will have to correlate between oe.orderse.order_id and customer_information.orderid to get

SELECT
CUST_LAST_NAME,
CUST_EMAIL,
ORDER_ID,
SUM(QUANTITY) AS QTY

FROM
(SELECT   
CUST_LAST_NAME,
CUST_EMAIL,
ORDERS.ORDER_ID,
PRODUCT_ID,
QUANTITY
  
FROM
OE.CUSTOMERS INNER JOIN OE.ORDERS
ON OE.CUSTOMERS.CUSTOMER_ID = OE.ORDERS.CUSTOMER_ID
INNER JOIN OE.ORDER_ITEMS
ON OE.ORDERS.ORDER_ID = OE.ORDER_ITEMS.ORDER_ID
ORDER BY ORDER_ID ASC) CTE

GROUP BY CUST_LAST_NAME, CUST_EMAIL, ORDER_ID

Answer 1

If you have any doubts, please give me comment...

I added UNIT_PRICE in CTE, because we need to multiply quantity with unit price of product in order to calculate order total. please change name of price field according to your database schema.

Answer:

SELECT CUST_LAST_NAME, CUST_EMAIL, ORDER_ID, SUM(QUANTITY) AS QTY, SUM(QUANTITY*UNIT_PRICE) AS OrderTotal

FROM (SELECT CUST_LAST_NAME, CUST_EMAIL, ORDERS.ORDER_ID, PRODUCT_ID, QUANTITY, UNIT_PRICE

    FROM OE.CUSTOMERS INNER JOIN OE.ORDERS ON OE.CUSTOMERS.CUSTOMER_ID = OE.ORDERS.CUSTOMER_ID INNER JOIN OE.ORDER_ITEMS ON OE.ORDERS.ORDER_ID = OE.ORDER_ITEMS.ORDER_ID ORDER BY ORDER_ID ASC) CTE

WHERE EXISTS(

    SELECT *

    FROM OE.ORDERS

    WHERE OE.ORDERS.ORDER_ID = CTE.ORDER_ID

);