SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    TN
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:
    Code:
    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.
    -Mike

    - In the mood for some funny pictures or videos?
    - Find the best websites and share yours.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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"?

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    TN
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code:
    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).
    -Mike

    - In the mood for some funny pictures or videos?
    - Find the best websites and share yours.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by mikeathey View Post
    Code:
    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?

  5. #5
    SitePoint Enthusiast
    Join Date
    Oct 2001
    Location
    TN
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    -Mike

    - In the mood for some funny pictures or videos?
    - Find the best websites and share yours.

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •