Calculate Total Price From Data Of 2 Tables

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 ---------------------------------------------------------

Solution 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 
GROUPBY sd.PR_KEY, sd.FR_KEY;

No JOIN is necessary. But GROUP BY is.

Solution 2:

You can try query below for your second question.

SELECT
    SALE.PR_KEY,
    TRAN_ID,
    TRAN_NO,
    TRAN_DATE,
    CUSTOMER_ID,
    USER_ID,
    TABLE_ID,
    PAY_TYPE_ID,
    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

Solution 3:

Perfect oppertunity to use OUTER APPLY

SELECT 
   PR_KEY,
   FR_KEY,
   O.Grand_total 
FROM dbo.SALE OUTER APPLY 
(
SELECTSUM(PRICE * AMOUNT) AS Grand_total FROM SALE_DETAIL WHERE FR_KEY =PR_KEY
GROUPBY FR_KEY
) O
WHERE PR_KEY =@FR_KEY 
ORDERBY PR_KEY