Integrating COUNT() into a nested MySQL query

Question

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?

Thorsten Kettner · Accepted Answer · 2022-03-02 17:08:29Z

2

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;

answered Mar 2, 2022 at 17:08

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Trick Jarrett Over a year ago

Cheers! That works perfectly. I appreciate your help.

Collectives™ on Stack Overflow

Integrating COUNT() into a nested MySQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related