LEFT OUTER JOIN

Question

I have a table in Postgresql. I would like to count two columns where one has more record than the other. If I use LEFT OUTER JOIN I think it will work, however, I’m not succeeding with the Postgresql syntax. The command I’m using is as follows:

SELECT codmunic, ene_ine, count(loc) as energia_ine
from tabela a left join
    (SELECT codmunic, COUNT(loc) as energia_ine
      FROM tabela
      WHERE ene_ine = 's'
      GROUP BY codmunic) as ag_caim
      using (codmunic)
      group by codmunic;

Answer 1

Maybe you don’t need any kind of JOIN to make this comparative count, look at this:

SELECT
    codmunic,
    ene_ine,
    count(loc) as energia_ine
FROM
    tabela
GROUP BY
    codmunic,
    ene_ine
ORDER BY
    codmunic,
    ene_ine;