You can’t have a join in an update statement. It should be update articles set num_comments = (select count (*) from comments where comments.article_id = articles.id) This will update the entire articles table, which may not be what you want. If you intend to update only one article then add a ‘where’ clause after the … Read more
You need to use HAVING, not WHERE. The difference is: the WHERE clause filters which rows MySQL selects. Then MySQL groups the rows together and aggregates the numbers for your COUNT function. HAVING is like WHERE, only it happens after the COUNT value has been computed, so it’ll work as you expect. Rewrite your subquery … Read more
In all versions of MySQL, simply alias the aggregate in the SELECT list, and order by the alias: SELECT COUNT(id) AS theCount, `Tag` from `images-tags` GROUP BY `Tag` ORDER BY theCount DESC LIMIT 20