Mysql – Fetch first 9 records and make one more record which will be as “Others” in MySQL

MySQLoptimizationstored-procedures

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.

Best Answer

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);

Related Solutions

Mysql – Using count in triggers to update another table

This trigger does the job:

create trigger trx_updates_atrig
after insert on trx_updates for each row begin
DECLARE updatecount INT;
  set updatecount = ( select count(*) from trx_updates where request = new.request );
  if updatecount=4
    then
      update trx_request set finished=NOW() where id = new.request;
  end if;
end//

SQLFiddle Demo.

Mysql – Save dataset within another table best practice

If I understand well, you need the bill to still have the person's data when it was created. And if the person changed it's data, like address, you need the bill to keep having the original address where that person was billed.

In other words, you will have different 'versions' of the person. What I would is the following:

Divide person table into two tables with master-details (or one to many) relationship. In the master table, have the fields that do not change, like ID, Name. In the details table, have a primary key field and the ID from master and other fields that may change, like address.

Person(ID primary key, name , ...)
Person_det(ID PK, Person_id referencing person.id, address, ...)

'Bill' table now would have "person_det.id" as a foreign key, instead of person.id.