Post with average comments per hour

Vote count:

0

SELECT p.*,
    u.user_id,
    u.user_name,
    count(c.comment_post_id) AS comments
FROM posts AS p
LEFT JOIN comments AS c
    ON (p.post_id = c.comment_post_id)
LEFT JOIN users AS u
    ON (p.postedby_id = u.user_id)
WHERE c.comment_added > NOW() - INTERVAL 1 HOUR
GROUP BY p.post_id    
ORDER BY count(c.comment_post_id) DESC

This makes a list of posts sorted by most comments the last hour. The problem now is that the posts with 0 comments last hour does not make the list.

So is there a way to make it ordered by average comment per hour since the comment was created? This way all the posts will make the list, and the most discussed posts will always be on top.

asked 1 min ago

user3127399

5

Post with average comments per hour