I have the following nested MYSQL query. Currently, out of the results, I go through and tally the tag appearances to identify the frequency. I've tried to integrate MYSQL's COUNT() into my query but have been unable to.
Current query:
SELECT tagId
FROM refTags
WHERE postId IN (SELECT postId FROM refTags WHERE tagId = 1)
AND tagId <> 1
ORDER BY tagId ASC
The desired return is tagId, COUNT(tagId) sorted by COUNT(tagId) DESC.
Am I able to do this all in the same query or am I correct in doing the counting after the results return?
CodePudding user response:
You want one result row per tag ID, so group by tag ID. Then count.
SELECT tagId, COUNT(*) AS number_of_posts
FROM refTags
WHERE postId IN (SELECT postId FROM refTags WHERE tagId = 1)
AND tagId <> 1
GROUP BY tagId
ORDER BY COUNT(*) DESC;