Question 112774 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – combine a JOIN with the COUNT function and also distinguish between likes and dislikes

I have two tables with one referencing the other.

CREATE TABLE blogs (
    article LONGTEXT,
    id VARCHAR(255) PRIMARY KEY
);

CREATE TABLE blog_eval (
    blog_id VARCHAR(255) REFERENCES blogs(id) ON DELETE CASCADE,
    user_id VARCHAR(255) REFERENCES users(id),
    is_like BOOLEAN, --true if like / false if dislike
    PRIMARY KEY (blog_id, user_id)
);

I need all blogs with two additional columns: sum of likes and sum of dislikes.

How do I combine a JOIN with the COUNT function and also distinguish between likes and dislikes?

Tags:

3

Answers


  1. 0

    One option uses conditional counts:

    select b.*, e.cnt_dislike, e.cnt_dislike
from blogs b
left join (
    select blog_id, sum(is_like) cnt_like, sum(1 - is_like) cnt_dislike
    from blog_eval
    group by blog_id
) e on e.blog_id = b.id

    Since is_like is a boolean (an integer in essence), expression sum(is_like) counts the true values. Pre-aggregating in a subquery might help efficiency here.

    In recent MySQL versions (>= 8.0.14), we can express this with a lateral join, which might perform better:

    select b.*, e.*
from blogs b
left join lateral (
    select sum(e.is_like) cnt_like, sum(1 - e.is_like) cnt_dislike
    from blog_eval e
    where e.blog_id = b.id
) e on 1 = 1
    Login or Signup to reply.
  2. 0

    Join the two tables, and use SUM() to total the likes and dislikes.

    SELECT b.id, SUM(e.is_like) AS likes, SUM(NOT e.is_like) AS dislikes
FROM blogs AS b
LEFT JOIN blog_eval AS e ON e.blog_id = b.id
GROUP BY b.id
    Login or Signup to reply.
  3. 0

    Can’t you just get all the data you need from the one table?

    SELECT blog_id, is_like, count(*)
FROM blog_eval
GROUP BY blog_id, is_like;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search