SQL Join and Count can't GROUP BY right?

Question

So, let's say I want to select the identifier of all my blog posts and then the number of comments associated with this blog post, how can I use GROUP BY or ORDER BY so that the list returned is in the order of the number of comments per post?

Do I have this query that returns data, but not in the order I want? Changing a group makes no difference:

SELECT p.ID, count(c.comment_ID) 
FROM wp_posts p, wp_comments c 
WHERE p.ID = c.comment_post_ID 
GROUP BY c.comment_post_ID;

Answer 1

I am not familiar with the syntax of pre-SQL92, so I will express it so that I know:

SELECT c.comment_post_ID, COUNT(c.comment_ID)
FROM wp_comments c
GROUP BY c.comment_post_ID
ORDER BY COUNT(c.comment_ID) -- ASC or DESC

? SQL Server, , , . :

SELECT p.ID, COUNT(c.comment_ID)
FROM wp_posts p
JOIN wp_comments c ON c.comment_post_ID = p.ID
GROUP BY p.ID
ORDER BY COUNT(c.comment_ID)

Answer 2

SELECT p.ID, count(c.comment_ID) AS [count]
FROM wp_posts p, wp_comments c 
WHERE p.ID = c.comment_post_ID 
GROUP BY c.comment_post_ID;
ORDER BY [count] DESC

Answer 3

There are probably no related data in the comment table, so please try to group them by post ID and please study the JOIN instructions, this is very useful and gives better results.

SELECT p.ID, count(c.comment_ID) 
FROM wp_posts p
LEFT JOIN wp_comments c ON (p.ID = c.comment_post_ID)
GROUP BY p.ID

I also encountered this situation on my SQL query journeys :)