I'm glad to see that you found a solution. BTW, I was playing around a bit and using the direction provided by well intended user #id10t, and did the following:
Code: Select all
dbadmin=> SELECT * FROM test;
place1 | place2
--------+--------
A | A
A | B
B | B
C | A
D | C
(5 rows)
Code: Select all
dbadmin=> SELECT COALESCE(first.place, second.place) AS place, NVL(first.cnt, 0) AS first, NVL(second.cnt, 0) AS second
dbadmin-> FROM (SELECT place1 AS place, COUNT(*) AS cnt FROM test GROUP BY place1) first
dbadmin-> FULL OUTER JOIN (SELECT place2 AS place, COUNT(*) AS cnt FROM test GROUP BY place2) second
dbadmin-> ON second.place = first.place
dbadmin-> ORDER BY place;
place | first | second
-------+-------+--------
A | 2 | 2
B | 1 | 2
C | 1 | 1
D | 1 | 0
(4 rows)
Next I added an "E" value to the data set and an extra "A":
Code: Select all
dbadmin=> INSERT INTO test VALUES ('A', 'E');
OUTPUT
--------
1
(1 row)
Code: Select all
dbadmin=> SELECT COALESCE(first.place, second.place) AS place, NVL(first.cnt, 0) AS first, NVL(second.cnt, 0) AS second
dbadmin-> FROM (SELECT place1 AS place, COUNT(*) AS cnt FROM test GROUP BY place1) first
dbadmin-> FULL OUTER JOIN (SELECT place2 AS place, COUNT(*) AS cnt FROM test GROUP BY place2) second
dbadmin-> ON second.place = first.place
dbadmin-> ORDER BY place;
place | first | second
-------+-------+--------
A | 3 | 2
B | 1 | 2
C | 1 | 1
D | 1 | 0
E | 0 | 1
(5 rows)