Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

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

by chacham (Prior)
on Apr 03, 2017 at 19:09 UTC ( #1186883=note: print w/replies, xml ) Need Help??


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;
  • 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
    poj

      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;

        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

        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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1186883]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (7)
As of 2020-10-27 12:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My favourite web site is:












    Results (256 votes). Check out past polls.

    Notices?