POSTGRESQL : Combining two query result with different columns but same number of rows

Viewed 28

Im currently trying to combine 2 queries results in one. These 2 queries has the same number of rows and are group by the same field but has different column.

This works :

SELECT Distinct
MAX(d.libelle) AS libelle_dpt,
MAX(d.code_dpt) AS code_dep,
MAX(r.libelle) AS libelle_region,
MAX(pd.pop_dep) AS nb_habitants,MAX(ls.nb_canton) AS nb_canton
FROM election_2015.commune c
LEFT JOIN (

            SELECT MAX(c.code_canton) AS code_canton,Count(distinct c.code_canton) AS nb_canton 
            FROM election_2015.commune co
            JOIN election_2015.departement d
            ON co.code_dpt = d.code_dpt
            JOIN election_2015.canton c
            ON c.code_canton = co.code_canton
            GROUP BY d.code_dpt
            ORDER BY d.code_dpt ASC

    ) ls
ON ls.code_canton = c.code_canton
JOIN election_2015.departement d
ON d.code_dpt = c.code_dpt
JOIN election_2015.region r
ON d.code_region = r.code_region
JOIN election.popgent_all pd
ON pd.dep = d.code_dpt
GROUP BY d.code_dpt

But I was wondering if there is an other way to do this, maybe like an union but with rows? Something like this (not working cause queries hasn't the same number of columns) :

SELECT Distinct
MAX(d.libelle) AS libelle_dpt,
MAX(d.id) AS id_dep,MAX(d.code_dpt) AS code_dep,
MAX(r.libelle) AS libelle_region,
MAX(pd.pop_dep) AS nb_habitants
FROM election_2015.commune c
LEFT JOIN election_2015.departement d
ON d.code_dpt = c.code_dpt
LEFT JOIN election_2015.region r
ON d.code_region = r.code_region
LEFT JOIN election.popgent_all pd
ON pd.dep = d.code_dpt
GROUP BY d.code_dpt

UNION

SELECT Count(distinct c.code_canton) AS nb_canton FROM election_2015.commune co
JOIN election_2015.departement d
ON co.code_dpt = d.code_dpt
JOIN election_2015.canton c
ON c.code_canton = co.code_canton
GROUP BY d.code_dpt
ORDER BY election_2015.departement.code_dpt ASC

Thanks for any help. Alexandre

0 Answers
Related