>How can I combine the following into one SQL statement? The problem I am having is the majority of the data is coming from one table, but the criteria for each field is different.
>
>
>SELECT SUM(nloan_amount) as Total_Amt_CAP_Loans FROM loan.dbf WHERE UPPER(cstatus) = "ENROLLED" OR UPPER(cstatus) = "LOSS" OR UPPER(cstatus) = "PIF"
>
>SELECT SUM(nloan_amount) as Total_Amt_Enrolled_Loans FROM loan.dbf WHERE UPPER(cstatus) = "ENROLLED"
>
>SELECT SUM(nstate_reserve_payment) as nBDC_Reserve FROM loan.dbf WHERE UPPER(cstatus) = "ENROLLED" OR UPPER(cstatus) = "LOSS" OR UPPER(cstatus) = "PIF"
>
>SELECT SUM(nborrower_reserve_payment) + SUM(nlender_reserve_payment) as nBank_Borrower_Reserve FROM loan.dbf WHERE UPPER(cstatus) = "ENROLLED" OR UPPER(cstatus) = "LOSS" OR UPPER(cstatus) = "PIF"
>
>SELECT SUM(ytotal_claim_amount) as nClaims_Paid FROM claims.dbf
>
You can do this:
SELECT SUM(IIF(UPPER(cstatus) = "ENROLLED" OR UPPER(cstatus) = "LOSS" OR UPPER(cstatus) = "PIF",nloan_amount,0)) as Total_Amt_CAP_Loans, ;
SUM(IIF(UPPER(cstatus) = "ENROLLED",nloan_amount,0)) as Total_Amt_Enrolled_Loans, ;
SUM(IIF(UPPER(cstatus) = "ENROLLED" OR UPPER(cstatus) = "LOSS" OR UPPER(cstatus) = "PIF",nstate_reserve_payment,0)) as nBDC_Reserve, ;
SUM(IIF(UPPER(cstatus) = "ENROLLED" OR UPPER(cstatus) = "LOSS" OR UPPER(cstatus) = "PIF",nborrower_reserve_payment+nlender_reserve_payment,0)) as nBank_Borrower_Reserve ;
FROM loan.dbf
or better:
SELECT SUM(IIF(INLIST(UPPER(cstatus),"ENROLLED","LOSS","PIF"),nloan_amount,0)) as Total_Amt_CAP_Loans, ;
SUM(IIF(UPPER(cstatus) = "ENROLLED",nloan_amount,0)) as Total_Amt_Enrolled_Loans, ;
SUM(IIF(INLIST(UPPER(cstatus),"ENROLLED","LOSS","PIF"),nstate_reserve_payment,0)) as nBDC_Reserve, ;
SUM(IIF(INLIST(UPPER(cstatus),"ENROLLED","LOSS","PIF"),nborrower_reserve_payment+nlender_reserve_payment,0)) as nBank_Borrower_Reserve ;
FROM loan.dbf
Your last select is from another table, thus cannot be combined.
Lennert