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

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]

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

Replies are listed 'Best First'.
Re^7: How to add columns with new row name using perl from mysql query? by poj (Abbot) on Apr 04, 2017 at 05:47 UTC
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]
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]