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 just PRICE * AMOUNT, there is no sum here.

I apply It to create a view here is the query

    SELECT                       SALE_DETAIL.PR_KEY,SALE_DETAIL.FR_KEY,dbo.SALE_DETAIL.PRICE * dbo.SALE_DETAIL.AMOUNT AS Expr1, 
      
                         dbo.DM_ITEM.UNIT_ID, dbo.DM_ITEM.ACTIVE AS ACTIVE_ITEM, SUM(PRICE*AMOUNT) AS TOTAL_PRICE , dbo.SALE.TOTAL_CHAIR_USE,SALE_DETAIL.ITEM_ID,dbo.SALE_DETAIL.PRICE
FROM                     dbo.DM_ITEM 
                         INNER JOIN
                         dbo.SALE_DETAIL ON dbo.DM_ITEM.ITEM_ID = dbo.SALE_DETAIL.ITEM_ID 
                         LEFT OUTER JOIN
                         dbo.DM_VAT_TAX ON dbo.SALE_DETAIL.VAT_TAX_ID = dbo.DM_VAT_TAX.VAT_TAX_ID 
                         LEFT OUTER JOIN
                         dbo.SALE 
                         LEFT OUTER JOIN
                         dbo.DM_TABLES ON dbo.SALE.TABLE_ID = dbo.DM_TABLES.TABLE_ID ON dbo.SALE_DETAIL.FR_KEY = dbo.SALE.PR_KEY
WHERE                   (dbo.SALE.IS_PROVISIONAL = 1)
GROUP BY
    SALE.PR_KEY,DM_ITEM.ITEM_NAME,DM_ITEM.ITEM_CLASS_ID,DM_ITEM.UNIT_ID,
    SALE.TABLE_ID,DM_ITEM.ACTIVE,
    SALE.TRAN_ID,SALE_DETAIL.PRICE,
    TRAN_NO,PAY_TYPE_ID,DM_VAT_TAX.VAT_TAX_NAME,
    TRAN_DATE,SALE.COMMENT,
    CUSTOMER_ID,SALE_DETAIL.PR_KEY,SALE_DETAIL.FR_KEY,SALE_DETAIL.LIST_ORDER,SALE_DETAIL.ITEM_ID,SALE_DETAIL.VAT_TAX_ID,
    USER_ID,SALE.TOTAL_CUSTOMER_PAY,SALE.EXPORT_TYPE_ID,SALE.IS_PROVISIONAL,
    DM_TABLES.TABLE_NAME,DM_TABLES.TOTAL_CHAIR,SALE_DETAIL.AMOUNT,SALE.TOTAL_CHAIR_USE,SALE.COST_OTHER,SALE_DETAIL.HOUR_CALL

Here is the output

PR_KEY FR_KEY Expr1 UNIT_ID ACTIVE_ITEM TOTAL_PRICE TOTAL_CHAIR_USE ITEM_ID PRICE


 272.00 180.00  710000   7  0           710000      0               LAU_BO  355000.00
273.00  182.00  50000    5  0           50000       0               LON_R   50000.00
274.00  182.00  180000   5  0           180000      0               G_L     180000.00
281.00  187.00  30000    3  0           30000       0               9       10000.00
282.00  187.00  40000    3  0           40000       0               tom     20000.00
283.00  189.00  200000   3  0           200000      0               2       200000.00
295.00  199.00  0.00     0  1           0.00        0               5         0.00
296.00  199.00  0.00     5  0           0.00        0               B_XT    0.00

Could anyone help me out with this problem?

Answer

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
GROUP BY
    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;