SELECT TIPO_OPERACAO,
       CASE WHEN COUNT(1) > 0 THEN COUNT(1) ELSE 0 END AS qtd_iniciado
          ,SUM(
            CASE WHEN LOANSTATUS IN ('SIGNED', 'PAID') THEN 1 ELSE 0 END
          ) AS qtd_reservado
          ,SUM(
            CASE WHEN LOANSTATUS IN ('PAID') THEN 1 ELSE 0 END
          ) AS qtd_aprovado
          ,COALESCE(SUM(
            CASE WHEN TIPO_OPERACAO IN ('NOVO', 'REFIN', 'PORTABILIDADE') THEN vlr_contrato ELSE 0 END
          ),0) AS vlr_iniciado
          ,SUM(
            CASE WHEN TIPO_OPERACAO IN ('NOVO', 'REFIN', 'PORTABILIDADE')
                 AND LOANSTATUS IN ('SIGNED', 'PAID')
                      THEN vlr_contrato ELSE 0 END
          ) AS vlr_reservado
FROM (
     SELECT CASE
             WHEN L.LOANTYPE = 'NEW' THEN 'NOVO'
             WHEN L.LOANTYPE = 'REFIN' THEN 'REFIN'
             WHEN L.LOANTYPE = 'PORTABILITY' THEN 'PORTABILIDADE'
            END AS TIPO_OPERACAO,
            COALESCE (L.NETVALUE , 0) AS vlr_contrato,
            L.VALUEFORDEPOSIT AS vlr_troco,
            L.LOANSTATUS
      FROM CONTRACTLOAN L
            LEFT JOIN CONTRACT CONTRATO ON CONTRATO.ID = L.CONTRACTID
            LEFT JOIN PERSON P ON P.ID = PERSONID
      WHERE 1 = 1
        AND ($SYS_AGREEMENT$ = -1 OR P.AGREEMENTID = $SYS_AGREEMENT$)
        AND ((CASE $AGREEMENT$ WHEN -1 THEN 1 ELSE $AGREEMENT$ END) = P.AGREEMENTID OR $AGREEMENT$ = -1)
        AND L.LOANSTATUS NOT IN ('CANCELLED', 'CANCELLED_BY_SYSTEM')
        AND TRUNC(CONTRATO.CONTRACTDATE) BETWEEN $STARTDATE$ AND $ENDDATE$

)
GROUP BY TIPO_OPERACAO