CODEWITHSUNDEEP
sqlitejoincount
1chenar
1chenar

Reputation: 197

Join two tables in SQLite and Count

I have two tables named "likes" and "comments" and I want to have a table which has counts of likes and comments for each specific user, I wrote following query in SQLite but result is not true for all users, count values for users in both tables are multiple of number of likes and number of comments. 

SELECT 
  likes.liker_name, likes.liker_id, likes.profile_picture , 
  COUNT(comments.commenter_name) AS comment_count, COUNT( likes.liker_id) AS like_count
FROM likes  
LEFT JOIN comments 
  ON likes.liker_name = comments.commenter_name 
GROUP BY 
  likes.liker_name 
ORDER BY 
  COUNT( likes.liker_id) DESC

How can I get correct value of count for users that exist in both tables?

Upvotes: 2

Views: 938

Answers (1)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112392

The problem is: Some users have comments but no likes, others have likes but no comments, some have both and some have none. Therefore I suggest using a union query and summing that one again

SELECT
    u.name, u.id, u.profile_picture,
    SUM(u.like_count) AS like_count, SUM(u.comment_count) AS comment_count
FROM (
    SELECT
        liker_name AS name, liker_id AS id, profile_picture,
        COUNT(*) AS like_count, 0 AS comment_count
    FROM
        likes
    GROUP BY
        liker_name, liker_id, profile_picture
    UNION ALL
    SELECT
        commenter_name AS name, commenter_id AS id, profile_picture,
        0 AS like_count, COUNT(*) AS comment_count
    FROM
        comments
    GROUP BY
        commenter_name, commenter_id, profile_picture
) AS u
GROUP BY
    u.name, u.id, u.profile_picture

If you have a separate user table you could also left join the likes count and the comments count subqueries to the user table

SELECT
    u.name, u.id, u.profile_picture, l.cnt AS like_count, c.cnt AS comment_count
FROM
    users u
    LEFT JOIN
        (SELECT liker_id, COUNT(*) AS cnt
        FROM likes
        GROUP BY liker_id
        ) AS l
        ON u.user_id = l.liker_id
    LEFT JOIN
        (SELECT commenter_id, COUNT(*) AS cnt
        FROM comments
        GROUP BY commenter_id
        ) AS c
        ON u.user_id = c.commenter_id
WHERE l.cnt > 0 OR c.cnt > 0

No matter how you make it, you must count the comments and the likes in separate subqueries. If you count after joining you are summing on a result where records might be duplicated (the ones on the left side) and you are getting the wrong count.

Upvotes: 4

Related Questions