get count(*) from multiple tables sql

Question

I have three tables.

entry

ID   title
1    Entry1
2    Entry2
3    Entry3
4    Entry4

user_likes

ID   user_id    entry_id
1       1           3
2       3           1
3       9           4
4       2           2

user_bookmarks

ID   user_id    entry_id
1       6           3
2       4           3
3       2           1
4       2           2

What i want is the sum of likes and bookmarks for each entry.

result

entryID   likes    bookmarks
   1        1          1
   2        1          1
   3        1          2
   4        1          0

Also with total sum of likes and bookmarks of each entry.

result2

entryID   likes+bookmarks
   1            2
   2            2
   3            3
   4            1

I managed to get likes and bookmark result using this query in seperate tables. I was not able to show them together in a single table.

SELECT entry.id, COUNT(entry.id) AS likes FROM entry 
INNER JOIN user_like ON user_like.entry_id = entry.id GROUP BY entry.id ORDER BY likes DESC

You should edit your question and show what you have attempted. — Gordon Linoff
– Gordon Linoff, Commented May 7, 2017 at 13:09

Gordon Linoff · Accepted Answer · 2017-05-07 13:12:32Z

2

You should aggregate before joining:

select e.*, coalesce(l.likes, 0) as likes,
       coalesce(b.bookmarks, 0) as bookmarks,
       (coalesce(l.likes, 0) + coalesce(b.bookmarks, 0)) as both
from entries e left join
     (select entryid, count(*) as likes
      from likes l
      group by entryid
     ) l
     on l.entryid = e.id left join
     (select entryid, count(*) as bookmarks
      from bookmarks
      group by entryid
     ) b
     on b.entryid = e.id;

answered May 7, 2017 at 13:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

get count(*) from multiple tables sql

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