asked on

Weighted Average within SQL statement

The query below outputs data as follows (100,000 rows of it):
LPTG Division Loan Amount Count FICO MonthDate Lien Heloc
Fxd Gol      WER      116725.0000      1      426      12/2005        0      N
Fxd Gol       CLD      76637.7300      2      432      12/2005        0      N
BC      CLD      86317.0000      1      446      12/2005        2      N
Fxd Gol      CLD      250700.0000      1      449      12/2005        0      N
AP        TLD      112875.0000      1      451      12/2005        0      N
AP        TLD      212875.0000      1      751      12/2005        0      N

I need to get the weighted average of the FICO [sum(loan amount * FICO)/sum(loan amount)] grouped by Division, MonthDate, Lien, HELOC, and LPTG. i'm not sure how to incorporate this into my SQL statement (below). I know the FICO grouping would probably come out, but I'm not certain of that either. Let me know if further explanation is required. Thanks in advance!

SELECT
lptg.lptgshort,fld.Division AS Division,
sum(fld.PurchasedLoanAmt) AS Loan_Amount,
count(fld.PurchasedLoanAmt) AS Loan_Count,
fld.LoanFICOScore,
       CAST(fld.FundedDtTmMonth as Varchar(2))+'/'+CAST( fld.FundedDtTmyear as Varchar(4)) AS Month_Year,fld.lientype,Loanprog.HELOCPiggyBackOptionFlag

FROM fld (nolock)
--Inner Join EPSLoan (nolock)
-- on fld.loannum=EPSLoan.loannum
--Inner Join EPSReason (nolock)
-- on EPSReason.EPSReasonTypeId=EPSLoan.reasonforexception
--Left Join EPSRequest (nolock)
-- on fld.EPSRefNum = EPSRequest.EPSRefNum
Inner Join lptg on lptg.lptgid=fld.lptgid
Inner Join LoanProg on loanprog.loanprogid=fld.loanprogid

WHERE
-- fld.Division='CMD' --Use when querying specific division(s)
-- fld.Division='WLD' --Use when querying specific division(s)
-- AND
fld.IncludeFlag = 'Y'
AND fld.EventCd in('FN', 'FA')
-- AND fld.EPSRefNum is not null
-- AND fld.EPSRefNum <> 0
-- AND fld.EPSRefNum <> ''
AND ( (fld.FundedDtTmMonth in ('12') and fld.FundedDtTmyear = '2005')
OR (fld.FundedDtTmMonth in ('1','2','3','4','5','6','7','8','9','10','11','12') and fld.FundedDtTmYear='2006' )
)
AND fld.loanficoscore is not null
AND fld.loanficoscore > 299
AND fld.loanficoscore < 901

GROUP BY
fld.Division, -- Remove if specifying division(s) within WHERE statement
fld.FundedDtTmMonth,
fld.FundedDtTmYear,
       fld.LoanFICOScore,
lptg.lptgshort,
fld.lientype,
Loanprog.HELOCPiggyBackOptionFlag

ORDER BY
fld.Division,
fld.FundedDtTmYear,
       fld.FundedDtTmMonth,
fld.LoanFICOScore

GGuzdziol

Maybe sth like

SELECT Division, MonthDate, Lien, HELOC, LPTG, sum(loan amount * FICO) / sum(loan amount)
FROM (
<Your query here>
) a
GROUP BY Division, MonthDate, Lien, HELOC, LPTG

GGuzdziol

Or, if You insist on making it part of You query

SELECT lptg.lptgshort,fld.Division AS Division,
sum(fld.PurchasedLoanAmt * fld.LoanFICOScore) / sum(fld.PurchasedLoanAmt) AS Loan_Amount,
count(fld.PurchasedLoanAmt) AS Loan_Count,
MAX(CAST(fld.FundedDtTmMonth as Varchar(2))+'/'+CAST( fld.FundedDtTmyear as Varchar(4))) AS Month_Year,
fld.lientype,Loanprog.HELOCPiggyBackOptionFlag
FROM fld (nolock)
-- Inner Join EPSLoan (nolock)
-- on fld.loannum=EPSLoan.loannum
-- Inner Join EPSReason (nolock)
-- on EPSReason.EPSReasonTypeId=EPSLoan.reasonforexception
-- Left Join EPSRequest (nolock)
-- on fld.EPSRefNum = EPSRequest.EPSRefNum
Inner Join lptg on lptg.lptgid=fld.lptgid
Inner Join LoanProg on loanprog.loanprogid=fld.loanprogid
WHERE
-- fld.Division='CMD' --Use when querying specific division(s)
-- fld.Division='WLD' --Use when querying specific division(s)
-- AND
fld.IncludeFlag = 'Y'
AND fld.EventCd in('FN', 'FA')
-- AND fld.EPSRefNum is not null
-- AND fld.EPSRefNum <> 0
-- AND fld.EPSRefNum <> ''
AND ( (fld.FundedDtTmMonth in ('12') and fld.FundedDtTmyear = '2005')
OR (fld.FundedDtTmMonth in ('1','2','3','4','5','6','7','8','9','10','11','12') and fld.FundedDtTmYear='2006' )
)
AND fld.loanficoscore is not null
AND fld.loanficoscore > 299
AND fld.loanficoscore < 901

GROUP BY
fld.Division, -- Remove if specifying division(s) within WHERE statement
fld.FundedDtTmMonth,
fld.FundedDtTmYear,
lptg.lptgshort,
fld.lientype,
Loanprog.HELOCPiggyBackOptionFlag
ORDER BY
fld.Division,
fld.FundedDtTmYear,
fld.FundedDtTmMonth

srejja

ASKER

It appears to be almost working, but I am getting a divide by zero error.

GGuzdziol

Maybe You should change

sum(fld.PurchasedLoanAmt * fld.LoanFICOScore) / sum(fld.PurchasedLoanAmt)

to sth like (put anything what You like after ,,when 0 then'')

case sum(fld.PurchasedLoanAmt) when 0 then 0 else sum(fld.PurchasedLoanAmt * fld.LoanFICOScore) / sum(fld.PurchasedLoanAmt) end

or leave it as is and eliminate those rows:

...
having sum(fld.PurchasedLoanAmt) > 0

srejja

ASKER

It works. Thank you. What was the purpose of using the MAX function here:

MAX(CAST(fld.FundedDtTmMonth as Varchar(2))+'/'+CAST( fld.FundedDtTmyear as Varchar(4))) AS Month_Year,

ASKER CERTIFIED SOLUTION

GGuzdziol

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial