Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
add comment

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

share|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
add comment

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);
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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