kukiko11 kukiko11 - 1 month ago 7
MySQL Question

How can I group mysql results based on a calculated colum?

I'm trying to show the most commented posts of my web but is being impossible .-. I just got this mysql error all the time (Can't group on 'comments')

the relation between tables is:

table: post / colums: id_post, title, id_comment

table : comment / colums: id_comment, text, id_post

and this is the query I'm trying to use

SELECT p.title AS title, COUNT(c.id_comment) AS comments
FROM post p
INNER JOIN comment c ON p.id_post=c.id_post
GROUP BY comments DESC


Please any alternative or solution for this?

Answer

Why would you want to group by on the number of comments? You need order by clause to get the most commented to the top and group by posts:

SELECT p.title AS title, COUNT(c.id_comment) AS comments
FROM post p
INNER JOIN comment c ON p.id_post=c.id_post
GROUP BY p.id_post, p.title
ORDER BY comments DESC

You may want to have a limit clause as well to get the top N commented posts only.