CODEWITHSUNDEEP
sqlpostgresql
Matias
Matias

Reputation: 332

How to make a single view with two tables alike

Hello so I've these two queries alike, but at the moment to unite them in a view, i get trash or some weird multiplications.

SELECT ra.agno,cod_ine_reg, SUM(ra.cantidad) AS total_aprehendidos
FROM registra_aprehendidos ra JOIN comuna USING (id_pref)
JOIN provincia USING (cod_ine_prov)
JOIN region USING (cod_ine_reg)
GROUP BY ra.agno, cod_ine_reg;

https://i.imgur.com/TaFHBdo.png

SELECT rd.agno,cod_ine_reg, SUM(rd.cantidad) AS total_denuncias
FROM registra_denuncias rd JOIN comuna USING (id_pref)
JOIN provincia USING (cod_ine_prov)
JOIN region USING (cod_ine_reg)
GROUP BY rd.agno, cod_ine_reg;

https://i.imgur.com/dyRe0WF.png

Basically I can't do this, any idea on how to do it? I've tried using unite, unite all and some variants without results.

https://i.imgur.com/0WXieY5.png

Upvotes: 0

Views: 32

Answers (1)

Esteban P.
Esteban P.

Reputation: 2809

should work like this:

SELECT   apre.agno
        ,apre.cod_ine_reg
        ,apre.total_aprehendidos
        ,denu.total_denuncias
  FROM  (    
            SELECT ra.agno,cod_ine_reg, SUM(ra.cantidad) AS total_aprehendidos
            FROM registra_aprehendidos ra JOIN comuna USING (id_pref)
            JOIN provincia USING (cod_ine_prov)
            JOIN region USING (cod_ine_reg)
            GROUP BY ra.agno, cod_ine_reg     
         ) apre
LEFT OUTER JOIN
        (
            SELECT rd.agno,cod_ine_reg, SUM(rd.cantidad) AS total_denuncias
            FROM registra_denuncias rd JOIN comuna USING (id_pref)
            JOIN provincia USING (cod_ine_prov)
            JOIN region USING (cod_ine_reg)
            GROUP BY rd.agno, cod_ine_reg
        ) denu
     ON apre.agno = denu.agno
    AND apre.cod_ine_reg = denu.cod_ine_reg

if you expect results from registra_denuncias, without matching records in registra_aprehendidos, a FULL JOIN would be the better approach like:

SELECT   COALESCE(apre.agno, denu.agno) as agno
        ,COALESCE(apre.cod_ine_reg, denu.cod_ine_reg) as cod_ine_reg
        ,COALESCE(apre.total_aprehendidos,0) as total_aprehendidos
        ,COALESCE(denu.total_denuncias,0) as total_denuncias
  FROM  (    
            SELECT ra.agno,cod_ine_reg, SUM(ra.cantidad) AS total_aprehendidos
            FROM registra_aprehendidos ra JOIN comuna USING (id_pref)
            JOIN provincia USING (cod_ine_prov)
            JOIN region USING (cod_ine_reg)
            GROUP BY ra.agno, cod_ine_reg     
         ) apre
FULL OUTER JOIN
        (
            SELECT rd.agno,cod_ine_reg, SUM(rd.cantidad) AS total_denuncias
            FROM registra_denuncias rd JOIN comuna USING (id_pref)
            JOIN provincia USING (cod_ine_prov)
            JOIN region USING (cod_ine_reg)
            GROUP BY rd.agno, cod_ine_reg
        ) denu
     ON apre.agno = denu.agno
    AND apre.cod_ine_reg = denu.cod_ine_reg

Upvotes: 1

Related Questions