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

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.

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

Replies are listed 'Best First'.
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]

Replies are listed 'Best First'.

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]

[reply]
[d/l]
[select]