SQL Query help...

tjones1105 · #1 (**permalink**) 08-26-09, 17:30

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

r937 · #2 (**permalink**) 08-26-09, 20:12

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?

tjones1105 · #3 (**permalink**) 08-27-09, 18:57

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

r937 · #4 (**permalink**) 08-27-09, 19:10

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

Internet Services

Web Development

Digital Marketing

Consumer Tech