SELECT a.ProjectName,a.ProjectNo,COUNT(CASE WHEN b.loanLevel = 1 THEN 1 ELSE null END) as LNum,COUNT( DISTINCT b.ParentLoanNo)as LoanNum
, COUNT(CASE WHEN b.loanLevel = 1 THEN 1 ELSE null END)-COUNT(DISTINCT b.ParentLoanNo) as NoLoanNum FROM LoanManagement.dbo.ProjectBasic a
LEFT JOIN LoanManagement.dbo.Loan b ON a.ProjectNo = b.ProjectNo
GROUP BY a.ProjectName,a.ProjectNo