Return COUNT in a request

I have this request :

  SELECT id_user
    FROM posts
GROUP BY id_user
ORDER BY COUNT(*) DESC

...which will return the id_user, ordered by t开发者_Python百科heir number of occurrence in the posts table.

But along with the id_user information, I would like to keep track of the COUNT(*) and store it somewhere, but I have no idea how to do it.

Use:

  SELECT id_user, 
         COUNT(*) AS numPosts
    FROM posts
GROUP BY id_user
ORDER BY COUNT(*) DESC

The column alias in the example, numPosts, can then be referenced in whatever you're already using to get the id_user column values.

you can do:

  select id_user, count(*) total_count
    FROM posts
GROUP BY id_user
ORDER BY COUNT(*) DESC

That way you can still retrieve the user id and the total times it appeared in the table