Problema con consulta

[scooby151]

Hola...

Si lo que necesitas es que te aparezcan las comunidades sin que se te repitan, deberias de hacer un select externo, algo como lo siguiente

Código SQL:

SELECT HFSLAND_DES, SUM(contratos)
FROM (SELECT DISTINCT L.HFSLAND_DES, COUNT(c.id_cont) AS "contratos"
FROM contrat c, acteur a, td_address_back ab, hfs_lands l, tu_province p
WHERE C.ID_ACTR_CLIENT = a.id_actr
AND a.ADRB_ID = AB.ADRB_ID (+)
AND AB.PRV_VA_CD_FK = L.PRV_CD_FK (+)
AND AB.PRV_VA_CD_FK = P.PRV_VA_CD (+)
AND C.DT_DEB_CONT BETWEEN to_date('01012008','DD/MM/YYYY') AND to_date('31122008','DD/MM/YYYY')
AND C.CT_ID_TLP = 'FNC'
GROUP BY L.HFSLAND_DES
UNION
SELECT DISTINCT L.HFSLAND_DES, COUNT(c.id_cont) AS "contratos"
FROM contrat c, ligne_de_credit lc, acteur a, td_address_back ab, hfs_lands l, tu_province p
WHERE c.id_lc = lc.id_lc
AND lc.id_actr = a.id_actr
AND a.ADRB_ID = AB.ADRB_ID (+)
AND AB.PRV_VA_CD_FK = L.PRV_CD_FK (+)
AND AB.PRV_VA_CD_FK = P.PRV_VA_CD (+)
AND C.DT_DEB_CONT BETWEEN to_date('01012008','DD/MM/YYYY') AND to_date('31122008','DD/MM/YYYY')
AND C.CT_ID_TLP = 'WSL'
)GROUP BY HFSLAND_DES
ORDER BY 1,2;
 

Si no estoy mal, asi te deberia de salir una sola linea por cada comunidad, con la suma de los contratos.

Saludos.