I want to make a histogram of the number of comments per user in January 2019 (including the once that haven't commented)

The tables I'm working with look like this:

I think I'm overcomplicating it. But here is my try:

#Here I'm counting how much comments are per person that have commented. 

CREATE TABLE aux AS
SELECT user_id, COUNT(user_id)
FROM Undostres
GROUP BY user_id;

#With the following code, I end up with a table with the missing values (ids that haven't commented) 

CREATE TABLE Test AS
SELECT DISTINCT user_id  1
FROM aux
WHERE user_id   1 NOT IN (SELECT DISTINCT user_id FROM aux);

ALTER TABLE Test RENAME COLUMN user_id  1 TO  ;
INSERT INTO Undostres (user_id) 
SELECT user_id  FROM Test;

It returns an error when I try to rename user_id 1 with other name. So I can't keep going.

Any suggestions would be great!

CodePudding user response：

I would do it this way:

CREATE TABLE aux AS
SELECT Users.user_id, COUNT(Undostres.user_id) AS count
FROM Users 
LEFT OUTER JOIN Undostres USING (user_id)
GROUP BY Users.user_id;

I am assuming you have a table Users that enumerates all your users, whether they have made any comments or not. The LEFT OUTER JOIN helps in this case, because if there are no comments for a given user, the user is still part of the result, and the COUNT is 0.