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.

  • So can I ask, is there a reason to not do this in the application code? It may be more maintainable to future devs for this to be written as a for loop in the code. This is only a question, not a criticism. – jcolebrand Feb 6 '11 at 20:35
  • wouldn't the logic be much easier to just separate it out into it's own query. SELECT ... FROM blah WHERE ... LIMIT 9; and SELECT COUNT() FROM blah WHERE ...; and just subtract 9 from the count? adding a lot of logic into the query may negatively impact speed compared to two separate queries. – Patrick Feb 8 '11 at 0:42
  • 1
    @Patrick, drachenstern.... Adding all remaining records for creating record for "Other" is well work if those remaining records are less. what if there are 10k of remaining records. if I run for-loop to add count of rows in application layer then it would be definitely slow. – Kammy Feb 8 '11 at 8:48
  • I think doing a COUNT() in SQL is pretty fast. I don't have any data but I think a COUNT() would be alot faster than all the logic in Marian's answer – Patrick Feb 8 '11 at 13:42

Suggestion 1

How about (untested)

SELECT designation, count(*)
  FROM designations 

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
  (SELECT designation, c, @n:=@n+1 row_number
     (SELECT designation,
             count(*) c
      FROM designations
      GROUP BY designation
      ORDER BY count(*) DESC) t1) t2
GROUP BY IF(row_number<10,row_number,10);
| improve this answer | |

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
SELECT SUM(cnt), 'Others' AS CountryCode
FROM Cities
WHERE id > 9

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

| improve this answer | |
  • MySQL does not support the 'WITH' clause, or CTE's (see this MySQL bug. You'll need to move this to being an inline view instead. – TML Aug 23 '11 at 20:11
  • No CTEs, no analytic functions, like ROW_NUMBER(). It's a challenge to translate. – ypercubeᵀᴹ May 31 '12 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.