Tested on MySQL

And added a missing comma, FROM, and table alias. (Oops!) Thank you. Why did you remove the case statement from the GROUP BY? That's the part that does the magical split. :) (Even if i did write it incorrectly.) If mysql won't support it there, it'd need to be added into the subquery, which begins to make it more confusing...

SELECT
    CASE WHEN @rownum <= @row_limit THEN queue_name ELSE 'others' END 
+queue_name,
    SUM(jobs_pend)    jobs_pend,
    SUM(jobs_run)    jobs_run
FROM
    (
     SELECT
        CASE WHEN @rownum <= @row_limit THEN @rownum ELSE @row_limit +
+ 1 END box,
        queue_name,
        jobs_pend,
        jobs_run
     FROM
        (
         SELECT
            @row_num := @row_num + 1,
            queue_name,
            jobs_pend,
            jobs_run
         FROM
            queues,
            (SELECT @row_num := 0, @row_limit := 5) r
         ORDER BY
            queue_name
        ) q
    ) p
GROUP BY
    box;
[download]

That returns 1 record (and same if I correct @rownum to @row_num)

queue_name jobs_pend jobs_run
others           471      457

If I run the sub-select without the CASE like this

SELECT
   @row_num, queue_name, jobs_pend, jobs_run
FROM
  (
  SELECT
    @row_num := @row_num + 1,
    queue_name, jobs_pend, jobs_run
  FROM
    queues, (SELECT @row_num := 0) r
  ) q
[download]

it returns

@row_num  queue_name  jobs_pend jobs_run
20        adice_long          5       39
20        adice_ncsim         0        6
20        adice_short       254      192
20        calibre             0        0
20        dsp_ncsim_gls       0        2
20        dsp_ncsim_hp        0        2
20        dsp_ncsim_lp        0        5
20        dsp_ncsim_mp        0        5
20        hcg_ncsim_comp      0        0
20        hcg_ncsim_hp        0        9
20        hcg_ncsim_lp        0        0
20        hcg_ncsim_mp        0        0
20        hcg_ncsim_short     0        0
20        ipdc_pte            0        0
20        ncsim_long         41       78
20        ncsim_lp            1        4
20        ncsim_short         0       84
20        normal            170       30
20        spectreRF           0        1
20        vcs                 0        0

poj

Thank you for all the testing. I really ought to get a mysql instance running to test on. Not knowing it very well and not testing it has led me to some embarrassing mistakes.

The purpose of the variable is supply an id for each record. The purpose of the CASE statement is to stop adding numbers at 6. So we get, 1, 2, 3, 4, 5, 6, 6, 6, .... After that, a simple GROUP BY is used. The reason i suggested it is threefold: to avoid rewriting the subquery, to not require any confusing LIMIT or OFFSET parameters, to allow a simple way to change the number. At this point, however, i wonder if any possible benefit is offset by the confusion.