SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot raymo's Avatar
    Join Date
    Feb 2002
    Location
    The High Country, Victoria, Australia
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SUBQUERY (?) issues

    I'm not positive my task requires a subquery, but I have the inclination it does.
    Problem is, I am using ASP with MySQL (which, for those who don't know, does not currently support subqueries).

    Here is my table.

    Code:
    refid   week  referrer  hits
    ============================
    2       1     url6      829
    2       7     url4      724
    1       1     url1      519
    1       7     url5      412
    1       7     url3      347
    2       1     url2      128
    This table has no primary key. However, refid, week, and referrer columns are 'unique' field types.

    Now, to the function of the table: it will log all hits pertaining to it's refid (eg page.asp?refid=2), week# and http referrer.

    What I wish to do is display the top 3 (imagine there are more than two refids in the example table :oP ) refids for the current week#, BUT where there are entries with same refid & week, hits should be added together to form one entry! Which effectively means we're ignoring referrer field in the interest of collating the data!

    So, looking at the above table example, week 1 would have user 2 in first place; user 1 in second. However, week 7 places user 1 in first place since his two entries add up more hits (412+316, compared to user 2 with 724). Oh, I should also note that combined hits shall be displayed alongside each top3 userid for the week.

    Is this possible under MySQL?

    The smart cookie who says yes gets a gold star! *g*

    ThankYOU!
    ride it like it's stolen

  2. #2
    SitePoint Addict
    Join Date
    Jun 2001
    Location
    Newcastle, England
    Posts
    268
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This should all be possible, in fact pretty simple, using GROUP BY's and SUM's.

    We start off with something like:

    SELECT SUM(hits) AS TOTAL_HITS, WEEK, REFID
    FROM TABLE_NAME
    GROUP BY WEEK, REFID

    The output of this from your table would be something like:

    Code:
    TOTAL_HITS  WEEK  REFID
    519         1     1
    759         7     1
    957         1     2
    724         7     2
    So you can see that this has grouped and collated the information correctly.

    After this, we simply need to apply an ORDER BY to get them in order, a LIMIT to restrict it to the top 3 entries, and possibly a WHERE clause if you only want one week at a time. So possible final queries are:

    Code:
    This will give results for every week in the database
    
    SELECT SUM(HITS) AS TOTAL_HITS, WEEK, REFID
    FROM TABLE_NAME
    GROUP BY WEEK, REFID
    ORDER BY WEEK DESC, HITS DESC
    LIMIT 3
    or
    Code:
    This will give results for the specified week
    
    SELECT SUM(HITS) AS TOTAL_HITS, WEEK, REFID
    FROM TABLE_NAME
    WHERE WEEK = [INSERT WEEK HERE]
    GROUP BY WEEK, REFID
    ORDER BY HITS DESC
    LIMIT 3
    Hope this helps .

    Disclaimer: I haven't tested this in MySQL as my dev machine currently hasn't got it installed, but it ought to work (fingers crossed!)
    Last edited by CrazyCrane; Apr 7, 2002 at 13:42.
    Nick Wilson [ - email - ]

  3. #3
    SitePoint Zealot raymo's Avatar
    Join Date
    Feb 2002
    Location
    The High Country, Victoria, Australia
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mmm you were right! Works like a charm!!!
    SUMs eh. Syntax is so simple, it rocks.

    Kudos to you good sir !
    ride it like it's stolen

  4. #4
    SitePoint Zealot raymo's Avatar
    Join Date
    Feb 2002
    Location
    The High Country, Victoria, Australia
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have another question.

    What's the best way to turn refids into their username counterparts? This will be looked up from another table, named users. Field is called username.

    Cheerio

    Edit:


    Done. A left join was the order of the day.
    Last edited by raymo; May 29, 2002 at 22:09.
    ride it like it's stolen


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
  •