SQL query to get count of likes, comments, reads and score for each post

Question

So I have 4 tables:

1. post
2. likes
3. comments
4. reads

Each table has the column post_id

I want to create a query, where for each post, i want to know the number of likes, the number of comments, and the number of reads
Finally, i want another column caled score, where i sum up the values in likes, comments and reads

Here is the sql query i have to do that:

SELECT posts.post_id,
       posts.title,
       count(likes.like_id) as likes,
       count(comments.comment_id) as comments,
       post_read_count.count as reads,
       (count(likes.like_id) + count(comments.comment_id) + post_read_count.count) as score
FROM community.posts
LEFT JOIN community.likes
    ON posts.post_id = likes.post_id
LEFT JOIN community.comments
    ON posts.post_id = comments.post_id
LEFT JOIN community.post_read_count
    ON posts.post_id = post_read_count.post_id
WHERE posts.is_deleted = false
GROUP BY posts.post_id, post_read_count.count
ORDER BY posts.post_id DESC
LIMIT 100

but i am unable to get the correct number of comments and the sum of items into score is not working properly

I am on postgres

heres a sample data:

posts:
post_id | title
101       some title
102       hello there
103       good day sir
104       good bye         
105       whats up 


likes:
like_id | post_id
1         101  
2         101
3         101
4         102
5         102
6         104
7         104
8         105
9         105
10        101

comments:
comment_id | post_id
1            103  
2            103
3            103
4            101
5            102
6            104
7            105
8            105
9            105
10           103

post_read_count:
post_id | count
101       12
102       54
103       76
104       23         
105       87 

desired output:

output:
post_id | title         | likes | comments | reads | score
101       some title      4       1          12      17
102       hello there     2       1          54      57
103       good day sir    0       4          76      80
104       good bye        2       1          23      26
105       whats up        2       3          87      92

Answer 1

Because you have one-to-many, I would use two CTEs to get your aggregate counts. You'll need to coalesce because some counts may be zero, and you cannot add a NULL with an integer.

Schema (PostgreSQL v13)

create table posts (
  post_id integer, 
  title varchar(20)
  );

insert into posts values 
(101, 'some title'),
(102, 'hello there'),
(103, 'good day sir'),
(104, 'good bye'),    
(105, 'whats up');

create table likes (
  like_id integer, 
  post_id integer
  );
  
insert into likes values 
(1, 101),
(2, 101),
(3, 101),
(4, 102),
(5, 102),
(6, 104),
(7, 104),
(8, 105),
(9, 105),
(10, 101);

create table comments (
  comment_id integer, 
  post_id integer
  );
  
insert into comments values 
(1, 103),
(2, 103),
(3, 103),
(4, 101),
(5, 102),
(6, 104),
(7, 105),
(8, 105),
(9, 105),
(10, 103);

create table post_read_count (
  post_id integer, 
  pcount integer
  );
  
insert into post_read_count values 
(101, 12),
(102, 54),
(103, 76),
(104, 23),      
(105, 87);

---

Query #1

with cte_likes as (
  select post_id, count(*) as total_likes
  from likes
  group by post_id
  ), 
  cte_comments as (
    select post_id, count(*) as total_comments
    from comments
    group by post_id
    )
select p.post_id, 
  p.title,
  coalesce(l.total_likes, 0) as likes, 
  coalesce(c.total_comments, 0) as comments, 
  coalesce(prc.pcount, 0) as reads, 
  coalesce(l.total_likes, 0) + coalesce(c.total_comments, 0) + coalesce(prc.pcount, 0) as score
from posts p
left join cte_likes l
  on p.post_id = l.post_id
left join cte_comments c
  on p.post_id = c.post_id
left join post_read_count prc
  on p.post_id = prc.post_id;

post_id	title	likes	comments	reads	score
101	some title	4	1	12	17
102	hello there	2	1	54	57
103	good day sir	0	4	76	80
104	good bye	2	1	23	26
105	whats up	2	3	87	92

---

View on DB Fiddle