Problema con consulta

[Kilofafeure]

Hola, estoy haciendo una consulta y tengo un problemilla, les explico. Tengo que obtener una comunidad autonoma con tantos contratos, mi problema es que tengo que hacerlo en 2 consultas y obtengo 2 veces cada comunidad con tantos contratos por cada una. Espero me puedan ayudar ya que llevo mucho rato con esto y no se me ocurre como solucionarlo. Gracias por adelantado.

Código:

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 L.HFSLAND_DES
order by 1, 2
;

Funciona igual con el distinct que sin el...

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

[Kilofafeure]

Gracias por contestar! pero como me dices tú me tira el siguiente error:

Código:

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
                        *
Error at line 1
ORA-00904: "CONTRATOS": invalid identifier

no deja contar contratos, ya había probado algo así...al final lo he conseguido de la siguiente manera:

Código:

select HFSLAND_DES, count(id_cont)
from
(select L.HFSLAND_DES, c.id_cont
from contrat c, acteur a, td_address_back ab, hfs_lands l
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 C.DT_DEB_CONT between to_date('01/01/2010','DD/MM/YYYY') and to_date('31/01/2010','DD/MM/YYYY')
and C.CT_ID_TLP = 'FNC'
union
select L.HFSLAND_DES, c.id_cont
from contrat c, ligne_de_credit lc, acteur a, td_address_back ab, hfs_lands l
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 C.DT_DEB_CONT between to_date('01/01/2010','DD/MM/YYYY') and to_date('31/01/2010','DD/MM/YYYY')
and C.CT_ID_TLP = 'WSL')
group by HFSLAND_DES