MySQL multiple SUM returning same value

I have a "bloomp" (like a post), when I select it from the database, I SUM the number of comments, likes and if the current user liked.

The problem is with the number of likes and comments. For example:

If the bloomp has 3 likes and 1 comment, the select return that it has 3 likes and 3 comments, and vice-versa.

Here's my query:

SELECT `bloomps`.`id` AS id,
       `bloomps`.`text` AS text,
       `bloomps`.`date` AS date,
       `images`.`id` AS image_id,
       `videos`.`id` AS video_id,
       `videos`.`provider` AS video_provider,
       `users`.`name` AS author_name,
       `users`.`picture` AS author_picture,
       SUM(CASE WHEN likes.bloomp_id IS NOT NULL THEN 1 ELSE 0 END) AS likes_number,
       SUM(CASE WHEN likes.bloomp_id IS NOT NULL AND likes.author_id = 1 THEN 1 ELSE 0 END) AS liked,
       SUM(CASE WHEN comments.bloomp_id IS NOT NULL THEN 1 ELSE 0 END) AS comments_number
FROM (`bloomps`)
INNER JOIN `users` ON `users`.`id` = `bloomps`.`author_id`
LEFT JOIN `likes` ON `likes`.`bloomp_id` = `bloomps`.`id`
LEFT JOIN `comments` ON `comments`.`bloomp_id` = `bloomps`.`id`
LEFT JOIN `images` ON `images`.`bloomp_id` = `bloomps`.`id`
LEFT JOIN `videos` ON `videos`.`bloomp_id` = `bloomps`.`id`
GROUP BY `bloomps`.`id`
ORDER BY `bloomps`.`id` ASC
LIMIT 10

This query:

SELECT `bloomps`.`id` AS id,
       `bloomps`.`text` AS text,
       `bloomps`.`date` AS date,
       `users`.`name` AS author_name,
       `users`.`picture` AS author_picture,
       SUM(CASE WHEN likes.bloomp_id IS NOT NULL THEN 1 ELSE 0 END) AS likes_number,
       SUM(CASE WHEN likes.bloomp_id IS NOT NULL AND likes.author_id = 1 THEN 1 ELSE 0 END) AS liked,
       SUM(CASE WHEN comments.bloomp_id IS NOT NULL THEN 1 ELSE 0 END) AS comments_number
FROM (`bloomps`)
INNER JOIN `users` ON `users`.`id` = `bloomps`.`author_id`
LEFT JOIN `likes` ON `likes`.`bloomp_id` = `bloomps`.`id`
LEFT JOIN `comments` ON `comments`.`bloomp_id` = `bloomps`.`id`
GROUP BY `bloomps`.`id`
ORDER BY `bloomps`.`id` ASC
LIMIT 10

Is returning:

回答1:

You have joined more than one tables to the main (bloomps) table and all these have 1-to-many relationship with it. The result is a mini-cartesian join and wrong counts when grouping.

You need to group each one separately in subqueries and then join these to the base table - and no grouping there. Try this:

SELECT 
    b.id
  , b.*                                -- whatever column from `bloomps`
  , COALESCE(l.cnt,0)   AS likes_number,
  , COALESCE(l.liked,0) AS liked,
  , COALESCE(c.cnt,0)   AS comments_number
FROM 
      bloomps AS b
   LEFT JOIN 
      ( SELECT bloomp_id
             , COUNT(*) AS cnt 
             , COUNT(CASE WHEN author_id = 1 THEN 1 END) AS liked
        FROM likes
        GROUP BY bloomp_id
      ) AS l
         ON l.bloomp_id = b.id
   LEFT JOIN 
      ( SELECT bloomp_id
             , COUNT(*) AS cnt 
        FROM comments
        GROUP BY bloomp_id
      ) AS c
         ON c.bloomp_id = b.id
                                         -- no need for GROUP BY
ORDER BY id ASC 
  LIMIT 10 ;

If you don't want subqueries, you could keep the query with the modifications below. Test which one is more efficient:

SELECT 
    b.id
  , b.test
  , b.date 
  , COUNT(DISTINCT l.id)  AS likes_number,
  , COUNT(DISTINCT CASE WHEN l.author=1 THEN l.id END) AS liked,
  , COUNT(DISTINCT c.id)  AS comments_number
FROM 
      bloomps AS b
   LEFT JOIN 
      likes AS l
         ON l.bloomp_id = b.id
   LEFT JOIN 
      comments AS c
         ON c.bloomp_id = b.id
GROUP BY b.id ASC                  -- MySQL non-standard syntax for
                                   -- GROUP BY b.id 
                                   -- ORDER BY b.id ASC 
  LIMIT 10 ;

回答2:

The other joins are messing up your count. For example, let's say one bloomp has two videos and two comments; as currently written, your query will return 4 rows, making it looks like there are 4 comments. To fix it, either remove all the other stuff (see below), or add it to the Group By clause.

This should give you the basic info -- ID, as well as comments and likes:

SELECT `bloomps`.`id` AS id,
   SUM(CASE WHEN likes.bloomp_id IS NOT NULL THEN 1 ELSE 0 END) AS likes_number,
   SUM(CASE WHEN comments.bloomp_id IS NOT NULL THEN 1 ELSE 0 END) AS comments_number
FROM (`bloomps`)
LEFT JOIN `likes` ON `likes`.`bloomp_id` = `bloomps`.`id`
LEFT JOIN `comments` ON `comments`.`bloomp_id` = `bloomps`.`id`
GROUP BY `bloomps`.`id`
ORDER BY `bloomps`.`id` ASC
LIMIT 10

回答3:

UPDATES :

SELECT table1.id, table1.likes_number, table2.comments_number FROM
    (SELECT b.id AS id, SUM(IF(l.bloomp_id IS NULL,0,1) 
     FROM bloomps b LEFT JOIN likes l ON b.id = l.bloomp_id GROUP BY b.id) AS table1
JOIN
    (SELECT b.id AS id, SUM(IF(c.bloomp_id IS NULL,0,1))
     FROM bloomps b LEFT JOIN comments c ON b.id = c.bloomp_id GROUP BY b.id) AS table2
ON table1.id = table2.id 
ORDER BY table1.id ASC
LIMIT 10;