Fetch first 9 records and make one more record which will be as "Others" in MySQL

Question

I am fetching records from one table with count of one field with other field as name. I want only 10 records. out of which 9 records give me field and its count. but i want to show 10 record as "Others" with all remaining fields with count. This is something like wrapping records. Something like below is table contents.

emp_id | designation

 1   |   software Engg.

 2   |   software Engg.

 3   |   Project Manager

not less than 10 designation.

And I want to show first 10 records as
Software Engineers  20
Project Manager     5
....
....
....
Others    50

Is there any way to make SQL Query for mysql db so that it will be fast and save time in application level where I am adding up record counts for "Others" ?

Please suggest how I can make it possible in effective way.

Answer 1

Suggestion 1

How about (untested)

SELECT designation, count(*)
  FROM designations 
 GROUP BY designation ORDER BY COUNT(*) DESC LIMIT 9

UNION SELECT "Others" designation, COUNT(*) 
        FROM designations
       WHERE designation NOT IN (
               SELECT designation
               FROM designations 
               GROUP BY designation 
               ORDER BY COUNT(*) DESC 
               LIMIT 9 )

Suggestion 2

This one is tested. It uses MySQL-specific variables. It's efficient.

SET @n=0;

SELECT IF(row_number<10, designation,'Others') name,
       sum(c) AS occurances
FROM
  (SELECT designation, c, @n:=@n+1 row_number
   FROM
     (SELECT designation,
             count(*) c
      FROM designations
      GROUP BY designation
      ORDER BY count(*) DESC) t1) t2
GROUP BY IF(row_number<10,row_number,10);

Answer 2

I'm not familiar with the MySQL flavor of the language, but I'm going to show you my solution in T-SQL (sql server).

WITH cities (Cnt, CountryCode, Id) AS (
  SELECT TOP 100 PERCENT COUNT(c.CityId) AS Cnt, c.CountryCode, ROW_NUMBER() OVER (ORDER BY COUNT(c.CityId) desc) AS ID
  FROM dbo.City c
  GROUP BY c.CountryCode
  ORDER BY 1 desc
)
SELECT c.Cnt, c.CountryCode
FROM Cities c
WHERE c.id <= 9
UNION
SELECT SUM(cnt), 'Others' AS CountryCode
FROM Cities
WHERE id > 9
ORDER BY cnt DESC

I wrote the code using one of my tables - City(CityId, CountryCode) and I know that the biggest challenge will be not to translate the query using your table, but to translate it to work in MySQl, because I don't know if there are CTEs there (CTE = common table expression, something like tables in memory built with keyword WITH).