Getting the SUM() of claps and at the same time counting bookmarks in one query

by

You need to aggregate before doing the joins. One method is:

SELECT p.*, u.name, u.avatar, 
       b.bookmarksCount, c.totalClaps
FROM posts p LEFT JOIN 
     users u
     ON u.id = p.owner LEFT JOIN
     (SELECT b.post_id, COUNT(*) as bookmarksCount
      FROM bookmarks b
      GROUP BY b.post_id
     ) b
     ON b.post_id = p.id LEFT JOIN
     (SELECT c.post_id, SUM(claps_count) as totalClaps
      FROM claps c
      GROUP BY c.post_id
     ) c
     ON c.post_id = p.id;

Note that no aggregation is needed in the outer query.

The above should have good performance if you are interested in all posts. If you are filtering the posts by any criteria, then correlated subqueries are probably a better solution:

SELECT p.*, u.name, u.avatar, 
       (SELECT COUNT(*)
        FROM bookmarks b
        WHERE b.post_id = p.id
       ) as bookmarksCount,
       (SELECT SUM(claps_count)
        FROM claps c
        WHERE c.post_id = p.id
       ) as totalClaps
FROM posts p LEFT JOIN 
     users u
     ON u.id = p.owner LEFT JOIN
      b
     ON b.post_id = p.id;

For this, you want indexes on bookmarks(post_id) and claps(post_id, claps_count).

More Related Contents:

  1. What is better subquery vs literal on IN clause in mysql
  2. Remove duplicate rows in MySQL
  3. MySQL DELETE FROM with subquery as condition
  4. Subqueries with EXISTS vs IN – MySQL
  5. MySQL/MariaDB – order by inside subquery
  6. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  7. Can you use an alias in the WHERE clause in mysql?
  8. Select values that meet different conditions on different rows?
  9. How to find similar results and sort by similarity?
  10. MySQL error: key specification without a key length
  11. Insert into multiple tables in one query
  12. generate_series() equivalent in MySQL
  13. Select row with most recent date per user
  14. MySQL: Can’t create table (errno: 150)
  15. Mysql query to dynamically convert rows to columns
  16. Why does MySQL report a syntax error on FULL OUTER JOIN?
  17. Error creating table: You have an error in your SQL syntax near ‘order( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id ‘ at line 1 [duplicate]
  18. Mixing ANSI 1992 JOINs and COMMAs in a query
  19. MySQL variable format for a “NOT IN” list of values
  20. Convert partially non-numeric text into number in MySQL query
  21. MySQL “IN” operator performance on (large?) number of values
  22. Order by COUNT per value
  23. MySQL Select last 7 days
  24. Second SELECT query if first SELECT returns 0 rows
  25. Difference between two table structure
  26. How to retrieve JSON data from MySQL?
  27. MySQL equivalent of Oracle’s SEQUENCE.NEXTVAL
  28. When to use a left outer join?
  29. SQL (MySQL) vs NoSQL (CouchDB) [closed]
  30. MySQL Multiple Joins in one query?

Leave a Comment