Histogram using to data tables (SQL query)

Question

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:

id	Name
1	Jose
2	Pedro
3	Juan
4	Sofia

user_id	Comment	Date
1	Hello	2018-10-02 11:00:03
3	Didn't Like it	2018-06-02 11:00:03
1	Not so bad	2018-10-22 11:00:03
2	Trash	2018-7-21 11:00:03

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!

Bill Karwin · Accepted Answer · 2022-06-24 23:59:50Z

2

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.

answered Jun 24, 2022 at 23:59

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Histogram using to data tables (SQL query)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related