Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Posts
    21

    Unanswered: SQL Query help...

    Hello,
    I'm trying to figure out how to Limit my results to the top 3 records returned. Now thats the simple part and I have it working just fine. But now I want to add one more result with a count of all remaining records.

    For example

    Code:
    Select user, Count(user) AS Totals
    From Sales
    Group By user
    Order By user DESC
    Limit 0,3
    Would give me
    joe 25
    steve 17
    amy 12

    But What I want is and cant figure out :-)
    joe 25
    steve 17
    amy 12
    Everyone Else 25

    I'm using MySQL 5.0.45

    Thanks,
    tom

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's a easy way to do it, but which requires a small amount of math in the application language

    Code:
    SELECT 2 AS row_type
         , user
         , COUNT(*) AS Totals
      FROM Sales
    GROUP 
        BY user
    UNION ALL
    SELECT 1 AS row_type
         , NULL
         , COUNT(*) AS Totals
      FROM Sales
    ORDER 
        BY row_type
         , user DESC
    LIMIT 4
    this query returns the totals for everybody as the first row, then the totals for the top 3 users as the 2nd, 3rd, and 4th row

    make sense?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Posts
    21
    Thanks,
    it kind of works. The only problem is that "Everyone Else"/null result is the total of all of the records not the remaining.

    Thanks,
    tom

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tjones1105
    The only problem is that "Everyone Else"/null result is the total of all of the records not the remaining.
    that's not a problem -- that's the way it's designed

    okay, your application logic will loop over the 4 rows in the result set, right?

    okay, to process the first row, store the number but don't print anything

    for each of the next three rows, print the number for that user, and subtract that number from the stored "everyone else" total

    after you have done the 4th row, i.e. the 3rd user, print what's left in "everyone else"

    neat, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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