I have two tables loan(custno,acno & clearbal) & customer (custno,pan & name). Custno is common in both tables.I want to generate PAN wise data.For which I have build below mentioned query to get pan wise loan outstanding, I want to have customer name in output, but several pans are mentioned in different customer IDs, So
if i add name in same query it splits the record.
Is possible to incorporate customer name which is having lower id like
if "aaa" pan in in cusid 1 & 2 so cistomer nume in 1 should be displayed & group by should be done on "aaa"
sample details
Customer Table
Custno Pan Name
1 aaa Kevin
2 bbb peter
3 aaa Josh
Loan Table
Custno acno clearbal
1 20 100
2 21 50
3 22 200
Output I am getting through Query
Pan Count Bal
aaa 2 300
bbb 1 50
What I want is
Pan Name Count Bal
aaa Kevin 2 300
bbb peter 1 50
select c.pan,count(L.Acno) as Count,sum(L.ClearBal) as bal from LOAN as L inner join customer as C
on L.CustNo=c.CustNo where l.clearbal<0 group by c.PAN order by bal desc
I have tried below mentioned query but output skips records having same pan card.
What I have tried:
select c.name,c.pan,count(L.Acno) as Count,sum(L.ClearBal) as bal from LON20190629 as L inner join customer as C
on L.CustNo=c.CustNo inner joiN
(
SELECT pan, MIN(CustNo) MinPoint
FROM customer
where len(pan)>6
GROUP BY PAN
) tbl1
ON tbl1.PAN = C.PAN
WHERE tbl1.MinPoint = C.CustNo and and l.ClearBal<0 group by c.PAN,c.Name order by bal desc