Joining and Grouping 2 column set

Moderator: NorbertKrupa

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Joining and Grouping 2 column set

Post by JimKnicely » Sun Jul 26, 2015 1:50 am

Hi #Beg1nner,

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)
Notice how I count "C" once in the second column. I think that your result set from your original had count of 0, but I wasn't sure why, as "C" appears in the second column once.

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)
Is this what your were expecting?
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica SQL”