Some background, I am making a table in Postgres 9.5 that counts the number of actions performed by a user and grouping these actions by month using date_trunc(). The counts for each individual action are divided into separate tables, following this format:
Feedback table:
id | month | feedback_counted
----+---------+-------------------
1 | 2 | 3
1 | 3 | 10
1 | 4 | 7
1 | 5 | 2
Comments table:
id | month | comments_counted
----+---------+-------------------
1 | 4 | 12
1 | 5 | 4
1 | 6 | 57
1 | 7 | 12
Ideally, I would like to do a FULL OUTER JOIN of these tables ON the "id" and "month" columns at the same time and produce this query:
Combined table:
id | month | feedback_counted | comments_counted
----+---------+--------------------+-------------------
1 | 2 | 3 |
1 | 3 | 10 |
1 | 4 | 7 | 12
1 | 5 | 2 | 4
1 | 6 | | 57
1 | 7 | | 12
However, my current query does not capture the feedback dates, displaying it like such:
Rollup table:
id | month | feedback_counted | comments_counted
----+---------+--------------------+-------------------
| | |
| | |
1 | 4 | 7 | 12
1 | 5 | 2 | 4
1 | 6 | | 57
1 | 7 | | 12
This is my current statement, note that it uses date_trunc in place of month. I add the action counts later, the main issue is somewhere here.
CREATE TABLE rollup_table AS
SELECT c.id, c.date_trunc
FROM comments_counted c FULL OUTER JOIN feedback_counted f
ON c.id = f.id AND c.date_trunc = f.date_trunc
GROUP BY c.id, c.date_trunc, f.id, f.date_trunc;
I'm a bit of a novice with SQL and am not sure how to fix this, any help would be appreciated.
Replace ON c.id = f.id AND c.month = f.month with USING(id, month).
SELECT id, month, feedback_counted, comments_counted
FROM comments c
FULL OUTER JOIN feedback f
USING(id, month);
id | month | feedback_counted | comments_counted
----+-------+------------------+------------------
1 | 2 | 3 |
1 | 3 | 10 |
1 | 4 | 7 | 12
1 | 5 | 2 | 4
1 | 6 | | 57
1 | 7 | | 12
(6 rows)
Test it in db<>fiddle.