Re^7: How to add columns with new row name using perl from mysql query?

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

Comment on Re^7: How to add columns with new row name using perl from mysql query? Select or Download Code

Replies are listed 'Best First'.
Re^8: How to add columns with new row name using perl from mysql query? by chacham (Prior) on Apr 04, 2017 at 19:44 UTC
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.	[reply]
Re^9: How to add columns with new row name using perl from mysql query? by poj (Abbot) on Apr 04, 2017 at 20:00 UTC
It works if you alias the id, then it's just a normal GROUP BY on the first column (1) to get the sums `SELECT CASE WHEN num <= @row_limit THEN queue_name ELSE 'others' END name, num, queue_name, jobs_pend, 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 ) q` [download] gives name num queue_name jobs_pend jobs_run adice_long 1 adice_long 5 39 adice_ncsim 2 adice_ncsim 0 6 adice_short 3 adice_short 254 192 calibre 4 calibre 0 0 dsp_ncsim_gls 5 dsp_ncsim_gls 0 2 others 6 dsp_ncsim_hp 0 2 others 7 dsp_ncsim_lp 0 5 others 8 dsp_ncsim_mp 0 5 others 9 hcg_ncsim_comp 0 0 others 10 hcg_ncsim_hp 0 9 others 11 hcg_ncsim_lp 0 0 others 12 hcg_ncsim_mp 0 0 others 13 hcg_ncsim_short 0 0 others 14 ipdc_pte 0 0 others 15 ncsim_long 41 78 others 16 ncsim_lp 1 4 others 17 ncsim_short 0 84 others 18 normal 170 30 others 19 spectreRF 0 1 others 20 vcs 0 0 [download] poj	[reply] [d/l] [select]