• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6738
  • Last Modified:

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
0
srejja
Asked:
srejja
  • 4
  • 2
1 Solution
 
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
 
srejjaAuthor Commented:
It appears to be almost working, but I am getting a divide by zero error.  
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now