MySQL to output only n number of rows and group the rest into n+1 th row

Question

Is it possible to build a SQL query to output only n number of rows and sum up the rest of the columns returned n+1 th row.

for instance, I have two columns returned from an sql query. First column is the Name and the second column contains the count values. The names are distinct and the counts are numbers.

The query should only return 3 rows and if there are more than 3 rows, they all should be summed up to a fourth row with the name as 'Others' and the counts of the rest of the rows summed up into the fourth row.

Kindly help me out. Thanks in advance.

(example)
Table
Name | Value
First - 12
Second - 11
Third - 9
Fourth - 15
Fifth - 13
Sixth - 8

Query result
Name | Value
First - 12
Second - 11
Third - 9
Others - 36

Is there a reason it _has_ to be one 1 query? I can be done, but its a bit kludgy for something that is basically very simple in 2 queries. — Wrikken, Jan 27 '14 at 06:28
anything is fine. Two queries with temp tables will also do. I just wanted a rough idea how it can be achieved. Thanks — pragaas, Jan 27 '14 at 06:33

score 0 · Answer 1 · edited May 23 '17 at 12:28

I believe it would look like:

SELECT DISTINCT TOP 3 Col1, Col2
FROM Table

Unless you were wanting to have the second column a count of things, then it would be

SELECT DISTINCT TOP 3 Col1, Count(Col2) AS "New Name"
FROM Table
GROUP BY Col1

This is under the assumption you ONLY want the top 3. If you do want a 4th row, do a UNION with another select statement that uses OFFSET, that way you can select all rows but the top 3.

Look here, here, and here for examples on TOP, OFFSET, and UNION.

Re-reading the first part of your question, if you are looking to have the TOP number change, you will most likely need to have it as a stored procedure with a variable passed that controls the number for the TOP and OFFSET. Use this as a reference.

score 0 · Answer 2 · answered Jan 27 '14 at 06:41

0

Try GROUP BY...WITH ROLLUP to sum all the rows count in n+1 row. Try below example.

SELECT IFNULL(a.id, 'Other'), SUM(1) ids
FROM tableA 
GROUP BY a.id WITH ROLLUP

answered Jan 27 '14 at 06:41

Saharsh Shah

28,687
8
48
83

score 0 · Answer 3 · answered Jan 27 '14 at 07:19

Could you try something like this :

select case(name) 
    when 'First' THEN 'First'
    when 'Second' THEN 'Second'
    when 'Third' THEN 'Third'
    else 'Others'
end name, sum(value) value
from table
group by case(name) 
    when 'First' THEN 'First'
    when 'Second' THEN 'Second'
    when 'Third' THEN 'Third'
    else 'Others'

MySQL to output only n number of rows and group the rest into n+1 th row

3 Answers3