Can't figure out how to create complex query with group by and count

[mikeathey]

I have a table usertags:
usertags
------------
- usertagid
- usertagsiteid
- usertagtext

I want to select the usertagtext and count of usertagtext grouped by usertagtext. I only want it to show usertagtext that also have a match in the usertagsiteid to a given value for usertagtext.

Not sure if that makes sense so here is an example:
usertagid | usertagsiteid | usertagtext
1 | 1 | 'search'
2 | 1 | 'search engine'
3 | 2 | 'news'

If i feed the query the usertagtext 'search' it should list:
'search engine', 1

If i feed the query the usertagtext 'search engine' it should list:
'search', 1

If i feed the query the usertagtext 'news' it should not return any results.

Here is what I have so far but it doesn't accept the usertagtext input into it:

SELECT DISTINCT usertagtext, COUNT(usertagtext) AS usertagsum FROM usertags GROUP BY usertagtext ORDER BY usertagsum DESC LIMIT 20

Let me know if that still doesn't make sense, I'm having a hard time trying to describe it. Hopefully you can figure out what I am looking for based on my examples.

[Dan Grossman]

It's not making sense to me. You haven't defined "match".

Why does "search" match "search engine" but not "search"?

Why does "search engine" match "search" but not "search engine"?

Why does "news" not match "news"?

[mikeathey]

Sorry, that really was confusing. Let's see if I can improve my question.

I have a table, 'usertags', full of tags, 'usertagtext', that coorespond to sites, 'usertagsiteid'.

I am trying to generate a distinct list of those usertagtext values and the count of how many there are and sort it by count like I have done here:

SELECT DISTINCT usertagtext, COUNT(usertagtext) AS usertagsum FROM usertags GROUP BY usertagtext ORDER BY usertagsum DESC LIMIT 20

That works for what I want. But now I want to alter it to return the same kind of list except be able to input a given usertagtext value and it will remove from the list all the records that have a usertagsiteid that doesn't have a cooresponding usertagtext value equal to what I inputted and then return a list just like my query code above does but with those removed sites not included in the list. I also don't want the result to include usertagtext that I inputted.

So to rephrase, I only want it to pull from sites that have a match to the value I input for a given usertagtext. For example if i have a site that has 3 tags, and none of those tags match the input value i give for tag, then it shouldn't pull results from that site at all. But if the input value i give for tag matches a tag that cooresponds to that site then i want to pull all the other tags for that site (and all other sites that do the same and combine all their results).

[guido2004]

SELECT DISTINCT usertagtext, COUNT(usertagtext) AS usertagsum FROM usertags GROUP BY usertagtext ORDER BY usertagsum DESC LIMIT 20

Don't use DISTINCT when you use GROUP BY. It makes no sense.

For example if i have a site that has 3 tags, and none of those tags match the input value i give for tag, then it shouldn't pull results from that site at all. But if the input value i give for tag matches a tag that cooresponds to that site then i want to pull all the other tags for that site (and all other sites that do the same and combine all their results).

I think this is clear
But: if there are two sites that have the given tag, do you want to sum the results of those two sites, or do you want to have the totals per tag per site?

[mikeathey]

Thanks for the tip about distinct and group by.

Yes, I want it to sum the results if there is more than one site with the same tag. I don't want the results to be per site, but for all tags globally. I just want to remove sites that don't have the given tag anywhere.

[guido2004]

SELECT 
    ut1.usertagtext
  , COUNT(ut1.usertagtext) AS usertagsum 
FROM usertags AS ut1 
INNER JOIN 
  (SELECT DISTINCT usertagsiteid
   FROM usertags
   WHERE usertagtext = '$usertagtext'
  ) AS ut2
ON ut1.usertagsiteid = ut2.usertagsiteid
GROUP BY ut1.usertagtext 
ORDER BY ut1.usertagsum 
DESC LIMIT 20