SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    May 2003
    Location
    US
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    A Query to Rank Totals

    For explinational purposes, lets say i have 30 categories, each with 50 entries... thats 1500 entries in a database. There is a field for clicks on each one of these entries. What i want to do is get the top 5 categories. What i need to do is total up each of the 50 entries' clicks and then grab the highest ranking category. How would i write a mysql query, or combination of queries, to complete this task

    Thanks

  2. #2
    SitePoint Wizard westmich's Avatar
    Join Date
    Mar 2000
    Location
    Muskegon, MI
    Posts
    2,328
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This might work although I didn't test it.

    Code:
    Select Count(e.Clicks) as Total, e.CategoryID
    From Categories c Join Entries e On c.CategoryID = e.CategoryID
    Group By e.CategoryID
    Westmich
    Smart Web Solutions for Smart Clients
    http://www.mindscapecreative.com

  3. #3
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What DBMS are you using? Both Oracle and MS SQL Server have SQL extensions which will allow it, but I think they're both different so you'd have to let us know which you're using.

    Also, if you want to save everyone time, go to the documentation for your DBMS and look up LAG LEAD or ROLLUP or GROUPING SETS. Those are the most common SQL extensions to do what you want.


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
  •