if 50 jobs were dumped into the queue system in-between runs of the separate queries the results would be quite different.

the sort on the sum seemed to be to list the queues with the largest combined total, but then to sort each sublist by the elements in run/pending separately

For each considered totally separate, if it were me, id just do it all in perl. 

use strict; use warnings; 
use DBI; 
use JSON 'encode_json';
use cheap::mysqls qw/sql_connect/; 

  my ($DBH,$rc) = sql_connect(id=>'finddata',dbi=>'mysql');
#  my ($DBH,$rc) = sql_connect(id=>'finddata',dbi=>'sqlite');


  $DBH->do('DROP view IF EXISTS datas ');
  $DBH->do('create  
            view datas as
                 select queue_name as name, 
                       jobs_pend   as data1, 
                       jobs_run    as data2
                   from queues
           '); 



  my @arr; 
    my $stmt="select name,data1,data2 from datas ";

    my $sth = $DBH->prepare( $stmt );
    $sth->execute() or die $sth->errstr;
    
    while(my @row_array=$sth->fetchrow_array)
    {
             push @arr,[@row_array]; 
    }
    $sth->finish;

  print sumup(5,1,\@arr)."\n"; 
  print sumup(5,2,\@arr)."\n"; 

  $DBH->do('DROP view IF EXISTS datas ');
  $DBH->disconnect();

exit; 
sub sumup { 
  my $testn=shift; 
  my $col=shift; 
  my $arr=shift; 
  my @sort1=sort {$arr->[$b][$col] <=> $arr->[$a][$col]} 0..$#$arr; 
  my @out1; 
  my $rc=0; 
  for my $rown (@sort1) { 
    if ($rc<=$testn) { push @out1,[$arr->[$rown][0],$arr->[$rown][$col
+]];}
    else {$out1[-1][1]+=$arr->[$rown][$col]}
    $rc++;
  } 
  $out1[$testn][0]='added'; 
  return encode_json( \@out1 );
}

[download]
Result 
[["adice_short","254"],["normal","170"],["ncsim_long","41"],["adice_lo
+ng","5"],["ncsim_lp","1"],["added",0]]
[["adice_short","192"],["ncsim_short","84"],["ncsim_long","78"],["adic
+e_long","39"],["normal","30"],["added",34]]

[download]

In reply to Re^6: perl sql to separate and sort the column separately? by huck
in thread perl sql to separate and sort the column separately? by gpssana

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.