in reply to Re^3: How to add columns with new row name using perl from mysql query?
in thread How to add columns with new row name using perl from mysql query?

Good solution, and excellent use of UNION ALL.

Based on you solution, i would like to provide another approach, that uses one query, and without LIMIT or OFFSET. It simply adds a row number. Though i do not know how mysql treats an ORDER BY clause in a subquery. Anyway, completely untested:

SELECT
    CASE WHEN @rownum <= @row_limit THEN queue_name ELSE 'others' END 
+queue_name,
    SUM(jobs_pend)    jobs_pend,
    SUM(jobs_run)    jobs_run
    (
     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
    )
GROUP BY
    CASE WHEN @rownum <= @row_limit THEN @rownum ELSE @rownum + 1 END;

[download]
  • Comment on Re^4: How to add columns with new row name using perl from mysql query?
  • Download Code

Replies are listed 'Best First'.
Re^5: How to add columns with new row name using perl from mysql query?
by poj (Abbot) on Apr 03, 2017 at 20:09 UTC

    Tested on MySQL

    SELECT
   CASE WHEN num <= @row_limit THEN queue_name ELSE 'others' END
   queue_name,
   sum(jobs_pend) jobs_pend,
   sum(jobs_run)  jobs_run
   FROM
    (
     SELECT
        @row_num := @row_num+1 as num,
        queue_name,
        jobs_pend,
        jobs_run
     FROM
        queues,
        (SELECT @row_num := 0,  @row_limit := 5) r
     ORDER BY
        queue_name
    ) q
    GROUP BY 1

    [download]
    poj
[reply]
[d/l]

      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]
[reply]
[d/l]

        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
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom