sql - Calculate total price from data of 2 tables

Question

I have data like:

Table SALE:

PR_KEY TRAN_ID   TRAN_NO  TRAN_DATE   CUSTOMER_ID USER_ID TABLE_ID PAY_TYPE_ID TOTAL_PRICE  
-------------------------------------------------------------------------------------------
187    SALE    130511164  2012-05-27    0000       ADMIN        59        1          0

Table Sale_detail

PR_KEY   FR_KEY    LIST_ORDER    ITEM_ID    PRICE     AMOUNT
--------------------------------------------------------------
281      187        0.0000       9          10000        3
282      187        0.0000       tom        20000        2

My question is:

1. Are there any ways for me to push data to sale.totalprice by calculating (SD.Price * SD.Amount)+(SD.Price * SD.Amount) (for the same FR_KEY)

2. Or create a view to show the total price, same calculation (SD.Price * SD.Amount)+ (SD.Price * SD.Amount) (for each FR_KEY)

This is for SQL Server, and FR_KEY of sale_detail references the Pr_key of the Sale table.

I tried -

SELECT dbo.SALE_DETAIL.PR_KEY,
dbo.SALE_DETAIL.FR_KEY, 
SUM(dbo.SALE_DETAIL.PRICE * dbo.SALE_DETAIL.AMOUNT) AS Grand_total 
FROM dbo.SALE_DETAIL, dbo.SALE JOIN SALE s 
      on s.PR_KEY = SALE_DETAIL.FR_KEY 
WHERE SALE_DETAIL.PR_KEY = @FR_KEY 
ORDER BY PR_KEY

The result I want is:

PR_KEY TRAN_ID USER_ID TABLE_ID PAY_TYPE_ID TOTAL_PRICE
187    SALE    ADMIN   59       1            70000

Answer 1

The query that you have provided can be simplified to:

SELECT sd.PR_KEY, sd.FR_KEY, 
       SUM(sd.PRICE * sd.AMOUNT) AS Grand_total 
FROM dbo.SALE_DETAIL sd 
      on s.PR_KEY = SALE_DETAIL.FR_KEY 
WHERE sd.PR_KEY = @FR_KEY 
GROUP BY sd.PR_KEY, sd.FR_KEY;

No JOIN is necessary. But GROUP BY is.