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
LVL 3
srejjaAsked:
Who is Participating?
 
GGuzdziolCommented:
Avoiding error caused by missing expression (this one: CAST(fld.FundedDtTmMonth as Varchar(2))+'/'+CAST( fld.FundedDtTmyear  as Varchar(4))) in group by clause. You can either add MAX (as I proposed) or add this to GROUP BY (You are already grouping by fld.FundedDtTmMonth and fld.FundedDtTmYear which determines value of Month_Year so it's just to make parser happy).
0
 
GGuzdziolCommented:
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
0
 
GGuzdziolCommented:
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
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
srejjaAuthor Commented:
It appears to be almost working, but I am getting a divide by zero error.  
0
 
GGuzdziolCommented:
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
0
 
srejjaAuthor Commented:
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,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.