Calculate Sum Of Price From 2 Table Sql Server

After asking about calculate the sum of the total price here and Its work but then went I add(SUM(PRICE*AMOUNT) AS TOTAL_PRICE) to create my own View, the Total_price = Expr1, It

Solution 1:

According to your problem here and the choices you provided on your previous question, pushing sum of prices from Sale_Detail to master Sale table seems more logical now. You can UPDATE (push) your master table by using the previous query with WITH function. This way, you can update total price in Sale table based on its primary key which is a foreign key in Sale_Detail as I understood.

You can adjust your grouping in Sale_Detail table according to your total price summation for the key and other column values.

Please be careful writing and posting queries with such syntax that affects the readability.

WITH Sales_CTE (PR_KEY, TOTAL_PRICE)
 AS
 (
SELECT
    Sale.PR_KEY,
    SUM(PRICE*AMOUNT) AS TOTAL_PRICE
FROM
    Sale_Detail
INNER JOIN Sale 
    ON Sale_Detail.FR_KEY = Sale.PR_KEY
GROUPBY
    Sale.PR_KEY,
    TRAN_ID,
    TRAN_NO,
    TRAN_DATE,
    CUSTOMER_ID,
    USER_ID,
    TABLE_ID,
    PAY_TYPE_ID
)
UPDATE
    Sale
SET
    Sale.TOTAL_PRICE = Sales_CTE.TOTAL_PRICE
FROM
    Sale
INNER JOIN
    Sales_CTE
ON 
    Sales_CTE.PR_KEY = Sale.PR_KEY;