I have two sql table Fee_Payable_to_Students and Fee_Receipt_Students
Fee_Payable_to_Students has value as
S_Adm_No | Student | FheadId |FHead | Apr | May | Jun |
1001 | Ramesh | 1 | Admission Fee | 25000 | 0 | 25000 |
1001 | Ramesh | 2 | Annual Fee | 6600 | 0 | 6600 |
1001 | Ramesh | 3 | Tuition Fee | 1900 |1900 | 1900 |
Fee_Receipt_Students
S_Adm_No | Student | FheadId |FHead | Apr | May | Jun |
1001 | Ramesh | 1 | Admission Fee | 25000 | 0 | 25000 |
1001 | Ramesh | 2 | Annual Fee | 6600 | 0 | 6600 |
Now I want to display as
S_Adm_No| Student | FheadId |FHead | Apr | May | Jun | Q1 |R_Amt|Bal |
1001 | Ramesh | 1 | Admission Fee | 25000 | 0 | 25000 |50000|63200|5700 |
1001 | Ramesh | 2 | Annual Fee | 6600 | 0 | 6600 |13200|63200|5700 |
1001 | Ramesh | 3 | Tuition Fee | 1900 |1900 | 1900 | 5700|63200|5700 |
Note: R_Amt comes from Fee_Receipt_Students as alias
my code is below. Some please help...
[edit]Code block added - OriginalGriff[/edit]
What I have tried:
select
P.S_Adm_No,P.S_Name ,P.fheadid,P.FHead,P.apr,P.may,P.Jun,
(P.apr+P.may+P.Jun)Q1,
SUM(R.Apr+R.May+R.Jun) R_Amt
FROM
Fee_Payable_to_Students AS P LEFT JOIN Fee_Receipt_Students AS R
ON P.S_Adm_no = R.S_Adm_no AND P.cls_SecId = R.cls_SecId
where P.S_Adm_No='1001'
group by
P.S_Name,P.fheadid,P.S_Adm_No,P.apr,P.may,P.Jun,P.FHead,R.S_Adm_No
order by P.S_Adm_No