Hi akhter,
Use sub query. Refer below sample query.
SQL
CREATE TABLE #tbl_Payable (P_ID int,I_ID int,T_Liter varchar(50),T_Amt int,C_ID int)
CREATE TABLE #Tbl_CasRec(Cs_ID int,I_ID int,Cs_Amt int,P_ID int,C_ID int)
INSERT INTO #tbl_Payable VALUES(1,101, 45,6234,2)
INSERT INTO #tbl_Payable VALUES(2,102, 15,6234,2)
INSERT INTO #tbl_Payable VALUES(3,103, 14,2214,2)
INSERT INTO #tbl_Payable VALUES(4,104, 11,435887,1)
INSERT INTO #Tbl_CasRec VALUES(1,101,2000,1,2)
INSERT INTO #Tbl_CasRec VALUES(2,102,500,2,2)
INSERT INTO #Tbl_CasRec VALUES(3,104,2000,4,1)
SELECT C_ID
,SUM(T_Amt) Inv_Amt
,(SELECT SUM(Cs_Amt) FROM #Tbl_CasRec r WHERE P.C_ID = r.C_ID) Rec_Amt
,SUM(T_Amt) - (SELECT SUM(Cs_Amt) FROM #Tbl_CasRec r WHERE P.C_ID = r.C_ID) Balance
FROM #tbl_Payable p GROUP BY C_ID
DROP TABLE #tbl_Payable
DROP TABLE #Tbl_CasRec
Output
| C_ID |
Inv_Amt |
Rec_Amt |
Balance |
| 1 |
435887 |
2000 |
433887 |
| 2 |
14682 |
2500 |
12182 |
