Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

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 ( #1186889=note: print w/replies, xml ) Need Help??


in reply to Re^4: 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?

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
  • Comment on Re^5: How to add columns with new row name using perl from mysql query?
  • Download Code

Replies are listed 'Best First'.
Re^6: How to add columns with new row name using perl from mysql query?
by chacham (Prior) on Apr 03, 2017 at 21:35 UTC

    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

        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.

Log In?
Username:
Password:

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

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












    Results (256 votes). Check out past polls.

    Notices?