SELECT * FROM (
    SELECT queue_name, jobs_pend, jobs_run
        FROM queues
        ORDER BY queue_name
        LIMIT 5
) UNION ALL
SELECT 'others', SUM(jobs_pend), SUM(jobs_run) FROM (
    SELECT jobs_pend, jobs_run
        FROM queues
    ORDER BY queue_name
    LIMIT 18446744073709551615 OFFSET 5
)
[download]

Note: Tested only in SQLite, but it seems the LIMIT syntax is the same as in MySQL according to a cursory glance at the documentation. (Well, in fact, the large number has to be lower in SQLite, but MySQL documentation shows this constant).

($q=q:Sq=~/;[c](.)(.)/;chr(-||-|5+lengthSq)`"S|oS2"`map{chr |+ord
}map{substrSq`S_+|`|}3E|-|`7**2-3:)=~y+S|`+$1,++print+eval$q,q,a,
[download]

Hi,
why you had included limit range so long?

Because you want to sum over all the remaining rows, but you don't know their number. So I used the maximal possible number of rows, as shown in the documentation.

($q=q:Sq=~/;[c](.)(.)/;chr(-||-|5+lengthSq)`"S|oS2"`map{chr |+ord
}map{substrSq`S_+|`|}3E|-|`7**2-3:)=~y+S|`+$1,++print+eval$q,q,a,
[download]

#!perl
use DBI; 
use strict; 

my $DBH = get_dbh();
my $sql = 'SELECT queue_name,jobs_pend,jobs_run 
           FROM queues';
my $sth = $DBH->prepare( $sql );
$sth->execute();

# input
my %table = ();
my $recno = 0;
while (my ($name,$pend,$run) = $sth->fetchrow_array){
  my $key = ($recno++ < 5) ? $name : 'other' ;
  $table{$key}{'pend'} += $pend;
  $table{$key}{'run'}  += $run;
}

# output
for my $key (sort keys %table){
  my @col = ($key);# col[0]
  $col[1] = $table{$key}{'pend'};
  $col[2] = $table{$key}{'run'};
  printf "| %-25s | %3d | %3d |\n",@col;
}
[download]

Note the error message for your select is wrong here

Note the error message for your select is wrong here

Hey at least its not "select *' anymore!

Re^3: How to calculate the column and print it in pie chart format using perl?

Reminds me of a pre-punk band i did some gigs with, went by "Mod Dirge", but then i started seeing them doing gigs as Yer Mom" as well, at the same time even. The answer as to "why?" was that when the clubs wouldnt book Mod Dirge anymore cuz of the rowdy crowd they brought with them, they would send another guy to the club to get them booked under "Yer Mom", and then they invited an even "rowdier" crowd. They got another dozen or two gigs that way too.

> Hey at least its not "select *' anymore!

Note that my solution involves SELECT * from a union of two tables both of which have the columns listed, using the same names for both the inner selects would obscure the already complex statement even more:

SELECT queue_name, jobs_pend, jobs_run FROM (
    SELECT queue_name, jobs_pend, jobs_run
        FROM queues
        ORDER BY queue_name
        LIMIT 5
) UNION ALL
SELECT 'others', SUM(jobs_pend) AS jobs_pend, SUM(jobs_run) AS jobs_ru
+n FROM (
    SELECT jobs_pend, jobs_run
        FROM queues
    ORDER BY queue_name
    LIMIT -1 OFFSET 5
)
[download]

($q=q:Sq=~/;[c](.)(.)/;chr(-||-|5+lengthSq)`"S|oS2"`map{chr |+ord
}map{substrSq`S_+|`|}3E|-|`7**2-3:)=~y+S|`+$1,++print+eval$q,q,a,
[download]

I have few doubts .Here you had used the queue_name and filtered with the first 5.
In case if i want to filter by their values with jobs_running instead of considering queue_name .
Obatined output:

+---------------+--------------+--------------+
| queue_name    | jobs_pend    | jobs_run     |
+---------------+--------------+--------------+
| adice_long    |            5 |           39 |
| adice_ncsim   |            0 |            6 |
| adice_short   |          254 |          192 |
| calibre       |            0 |            0 |
| dsp_ncsim_gls |            0 |            2 |
+---------------+--------------+--------------+
[download]

Expected output:

+---------------+--------------+--------------+
| queue_name    | jobs_pend    | jobs_run     |
+---------------+--------------+--------------+
| adice_short   |          254 |          192 |
| adice_long    |            5 |           39 |
| adice_ncsim   |            0 |            6 |
| calibre       |            0 |            0 |
| dsp_ncsim_gls |            0 |            2 |
| others        |         212  |          218 |
+---------------+--------------+--------------+
[download]


So i had tried with revamping the mysql query for the code which you had posted which as follows.But i couldnt get the expected output


use DBI; 
use strict; 

my $DBH = get_dbh();
my $sql = 'SELECT queue_name,jobs_pend,jobs_run 
           FROM queues order by jobs_run DESC';
my $sth = $DBH->prepare( $sql );
$sth->execute();

# input
my %table = ();
my $recno = 0;
while (my ($name,$pend,$run) = $sth->fetchrow_array){
  my $key = ($recno++ < 5) ? $name : 'other' ;
  $table{$key}{'pend'} += $pend;
  $table{$key}{'run'}  += $run;
}

# output
for my $key (sort keys %table){
  my @col = ($key);# col[0]
  $col[1] = $table{$key}{'pend'};
  $col[2] = $table{$key}{'run'};
  printf "| %-25s | %3d | %3d |\n",@col;
}
[download]

The hash won't preserve the sort order from the SQL so you have to retain that in an array which I have called @top

#!perl
use DBI; 
use strict; 

my $DBH = get_dbh();
my $sql = 'SELECT queue_name,jobs_pend,jobs_run 
           FROM queues 
           ORDER BY jobs_run DESC';
my $sth = $DBH->prepare( $sql );
$sth->execute();

# input
my %table = ();
my $recno = 0;
my @top   = ();
while (my ($name,$pend,$run) = $sth->fetchrow_array){
  my $key = ($recno++ < 5) ? $name : 'other' ;
  $table{$key}{'pend'} += $pend;
  $table{$key}{'run'}  += $run;
  push @top,$key unless ($top[-1] eq 'other');
}

# output
for my $key (@top){
  my @col = ($key);# col[0]
  $col[1] = $table{$key}{'pend'};
  $col[2] = $table{$key}{'run'};
  printf "| %-25s | %3d | %3d |\n",@col;
}
[download]

Update : you could just use an array without the hash

# input
my @top = ();
my $max = 5; my $recno = 0;
while (my ($name,$pend,$run) = $sth->fetchrow_array){
  my $ix = ($recno > $max) ? $max : $recno ;
  $top[$ix][0]  = $name;
  $top[$ix][1] += $pend;
  $top[$ix][2] += $run;
  ++$recno;
}
$top[$max][0]  = 'other';

# output
for (@top){
  printf "| %-25s | %3d | %3d |\n",@$_;
}
[download]

poj

There are two fast ordered-hash implementationson on CPAN: Hash::Ordered and MCE::Shared::Ordhash.

use strict;
use warnings;

use Hash::Ordered;
use Data::Dumper;

tie my %table, 'Hash::Ordered';

for my $key (qw( a_a b_b c_c d_d e_e )) {
   $table{$key}{'pend'} += 1;
   $table{$key}{'run'}  += 2;
}

print Dumper(\%table), "\n";
[download]

The following does the same thing via MCE::Shared::Ordhash (non-shared construction via Tie).

use strict;
use warnings;

use MCE::Shared::Ordhash;
use Data::Dumper;

tie my %table, 'MCE::Shared::Ordhash';

for my $key (qw( a_a b_b c_c d_d e_e )) {
   $table{$key}{'pend'} += 1;
   $table{$key}{'run'}  += 2;
}

print Dumper(\%table), "\n";
[download]

Both produce the following output. Notice how the first level keys have retained order.

$VAR1 = {
          'a_a' => {
                     'run' => 2,
                     'pend' => 1
                   },
          'b_b' => {
                     'pend' => 1,
                     'run' => 2
                   },
          'c_c' => {
                     'run' => 2,
                     'pend' => 1
                   },
          'd_d' => {
                     'run' => 2,
                     'pend' => 1
                   },
          'e_e' => {
                     'run' => 2,
                     'pend' => 1
                   }
        };
[download]

These modules are reasonably fast. The OO interface is faster when extra performance is desired. However, the TIE interface is nice when wanting the native hash look and feel. There's no reason why one cannot have both. MCE::Shared::Ordhash, via the overload mechanism, handles on-demand hash-dereferencing on the fly.

use strict;
use warnings;

use MCE::Shared::Ordhash;
use Data::Dumper;

my $table = MCE::Shared::Ordhash->new();

for my $key (qw( a_a b_b c_c d_d e_e )) {
   $table->{$key}{'pend'} += 1;
   $table->{$key}{'run'}  += 2;
}

print Dumper($table), "\n";
[download]

The table hash is the real MCE::Shared::Ordhash object and not something hidden behind the TIE interface. Therefore, a dump of it will give you the structure of the object.

$VAR1 = bless( [
                 {
                   'a_a' => {
                              'run' => 2,
                              'pend' => 1
                            },
                   'b_b' => {
                              'pend' => 1,
                              'run' => 2
                            },
                   'd_d' => {
                              'run' => 2,
                              'pend' => 1
                            },
                   'c_c' => {
                              'pend' => 1,
                              'run' => 2
                            },
                   'e_e' => {
                              'pend' => 1,
                              'run' => 2
                            }
                 },
                 [
                   'a_a',
                   'b_b',
                   'c_c',
                   'd_d',
                   'e_e'
                 ],
                 {},
                 \0,
                 \0,
                 {
                   'a_a' => $VAR1->[0]{'a_a'},
                   'b_b' => $VAR1->[0]{'b_b'},
                   'c_c' => $VAR1->[0]{'c_c'},
                   'd_d' => $VAR1->[0]{'d_d'},
                   'e_e' => $VAR1->[0]{'e_e'}
                 }
               ], 'MCE::Shared::Ordhash' );
[download]

If you have time, check them out. Do random deletes or anything pertaining to a hash. You will be pleased with the performance. In that case, one might find the following useful.

  use strict;
  use warnings;

  use Hash::Ordered;
  use MCE::Shared::Ordhash;
  use List::Util 'shuffle';
  use Time::HiRes 'time';

  my ($start, $total); srand 0;

  sub ready {
      my $time = time;
      $total  += $time - $start;
      $start   = $time;
  }

# my $oh = Hash::Ordered->new();
  my $oh = MCE::Shared::Ordhash->new();

  print ref($oh), "\n";

  my @keys1 = shuffle('aaaa'..'gggf');  # size: 109,674
  my @keys2 = shuffle('gggg'..'mmmm');  # size: 109,675
  my @keys3 = shuffle('nnnn'..'ttts');  # size: 109,674
  my @keys4 = shuffle('tttt'..'zzzz');  # size: 109,675

  $oh->set($_,$_) for ('a'..'m','_','n'..'z'); # add 26 or more keys
  $oh->delete('_');                            # has INDX afterwards

  $start = time;
  $oh->set($_,$_) for @keys1;
  printf "duration (set    ): %0.02f\n", time - $start;

  ready();
  $oh->push($_,$_) for @keys2;
  printf "duration (push   ): %0.02f\n", time - $start;

  ready();
  $oh->unshift($_,$_) for @keys3;
  printf "duration (unshift): %0.02f\n", time - $start;

  ready();
  $oh->merge(map {$_,$_} @keys4);
  printf "duration (merge  ): %0.02f\n", time - $start;

# ready();
# $oh->delete($_) for @keys2;
# printf "duration (delete ): %0.02f\n", time - $start;

  printf "total time        : %0.02f\n", $total += time - $start;
[download]

Results vary from system to system. The following were captured from a 2.6 GHz machine. Anyway, these are the fastest pure-Perl ordered-hash implementations on CPAN right now. The results show the time taken to complete each action. Thus, lower is faster.

Hash::Ordered
duration (set    ): 0.20
duration (push   ): 0.24
duration (unshift): 0.29
duration (merge  ): 0.23
total time        : 0.95

MCE::Shared::Ordhash
duration (set    ): 0.12
duration (push   ): 0.16
duration (unshift): 0.21
duration (merge  ): 0.15
total time        : 0.65
[download]

MCE::Shared::Ordhash has low overhead. The reason is that the internal INDX hash is populated only on-demand. There are no worries about delays, for example deleting keys. Compare these with Tie::IxHash. Do forward and reverse deletes. Not to forget random deletes.

Perl is fun.

Gee thats funny. Given

use strict; use warnings; 
use DBD::SQLite;
use DBI;

my $table='queues';
my $ccode='queue_name TEXT, jobs_pend INTEGER, jobs_run INTEGER'; 

my $storagefile='finddata';
my $dbh = DBI->connect( "dbi:SQLite:dbname=".$storagefile ) || die "Ca
+nnot connect: $storagefile $DBI::errstr";

remake($table,$ccode); 
my $insert; 
{
my $sql='INSERT INTO '.$table.'( queue_name , jobs_pend , jobs_run  ) 
+values( ?,?,?)';    
$insert=$dbh->prepare($sql);
}

while (my $line=<DATA>) { 
  $line=~s/ //g;
  my (undef,$qn,$jp,$jr)=split('[|]',$line); 
  $insert->execute($qn,$jp,$jr); 
  } 


my $select; 
{
      my $sql="select * from ".$table;
      $select=$dbh->prepare($sql);
} 
  
  $select->execute();
  while(my $row=$select->fetchrow_arrayref){
  printf "| %-25s | %3d | %3d |\n",@$row;
#    print join ('|',@$row)."\n"; 
    } # ROW 
  $select->finish;

exit; 
sub remake { 
  my $table=shift; 
  my $code=shift; 
  $table='queues' unless ($table); 
  $code='queue_name TEXT, jobs_pend INTEGER, jobs_run INTEGER'  unless
+ ($code); 
  $dbh->do("DROP TABLE IF EXISTS ".$table);
  my $sql="CREATE TABLE ".$table.'  ('.$code.')'; 
  print $sql."\n";
  $dbh->do($sql);
 } # redo; 

exit; 

__DATA__
| adice_long      |            5 |           39 |
| adice_ncsim     |            0 |            6 |
| adice_short     |          254 |          192 |
| calibre         |            0 |            0 |
| dsp_ncsim_gls   |            0 |            2 |
| dsp_ncsim_hp    |            0 |            2 |
| dsp_ncsim_lp    |            0 |            5 |
| dsp_ncsim_mp    |            0 |            5 |
| hcg_ncsim_comp  |            0 |            0 |
| hcg_ncsim_hp    |            0 |            9 |
| hcg_ncsim_lp    |            0 |            0 |
| hcg_ncsim_mp    |            0 |            0 |
| hcg_ncsim_short |            0 |            0 |
| ipdc_pte        |            0 |            0 |
| ncsim_long      |           41 |           78 |
| ncsim_lp        |            1 |            4 |
| ncsim_short     |            0 |           84 |
| normal          |          170 |           30 |
| spectreRF       |            0 |            1 |
| vcs             |            0 |            0 |
[download]

when i run

use strict; use warnings; 
use DBI; 

my $storagefile='finddata';
my $DBH = DBI->connect( "dbi:SQLite:dbname=".$storagefile ) || die "Ca
+nnot connect: $storagefile $DBI::errstr";

my $sql = 'SELECT queue_name,jobs_pend,jobs_run 
           FROM queues order by jobs_run DESC';
my $sth = $DBH->prepare( $sql );
$sth->execute();

# input
my %table = ();
my $recno = 0;
while (my ($name,$pend,$run) = $sth->fetchrow_array){
  my $key = ($recno++ < 5) ? $name : 'other' ;
  $table{$key}{'pend'} += $pend;
  $table{$key}{'run'}  += $run;
}

# output
for my $key (sort keys %table){
  my @col = ($key);# col[0]
  $col[1] = $table{$key}{'pend'};
  $col[2] = $table{$key}{'run'};
  printf "| %-25s | %3d | %3d |\n",@col;
}
[download]

i get

| adice_long                |   5 |  39 |
| adice_short               | 254 | 192 |
| ncsim_long                |  41 |  78 |
| ncsim_short               |   0 |  84 |
| normal                    | 170 |  30 |
| other                     |   1 |  34 |
[download]

See i got an other row that you said you didnt, and i displayed rows correctly that you didnt. Why didnt you post what you actually got? Are you that lazy? If you dont want to help us why should we help you? You dont even grovel correctly.

and of course poj is right about the sort order. in  sort keys %table adice_long comes before adice_short

You owe the PerlOracle a remote job entry queue (

$T I99,C=O
$S I99
[download]

)

Is it possible to add others column directly in the mysql query without modifying into the while loop changes using perl


sub pending_running_partition
{
    $var_data_running = "";
    $var_data_pending = "";
      my $str= shift;
    $DBH = &connect or die "Cannot connect to the sql server \n";
    $DBH->do("USE $str;");
    my $stmt="select queue_name,jobs_pending,jobs_running from queues 
+order by jobs_pending,jobs_running DESC ;";        
    my $sth = $DBH->prepare( $stmt );
     $sth->execute() or print "Could not insert_pending_running_partit
+ion";
     my $tmp = 0;
    while(my @row_array=$sth->fetchrow_array)
    {
        if ($tmp == 0) {
        $var_data_running .= "\[\"$row_array[0] \($row_array[2]\)\",$r
+ow_array[2]\]";
        $var_data_pending .= "\[\"$row_array[0] \($row_array[1]\)\",$r
+ow_array[1]\]";
        $tmp++;
        }
        else {
        $var_data_running .= ",\[\"$row_array[0] \($row_array[2]\)\",$
+row_array[2]\]";
        $var_data_pending .= ",\[\"$row_array[0] \($row_array[1]\)\",$
+row_array[1]\]";
        }
    
    }
    $sth->finish;
    $DBH->disconnect();
}
[download]

By the above mysql query i pick maximum to minimum level .Without modifying while loop is it possible to limit maximum 5 and remaining rows to be added and namew with new row called others.

Is it possible..

Yes

Without modifying while loop..

No ( unless you make the SQL more complex )

Note the error message for your select is still wrong here

$sth->execute() or print "Could not in7sert_run_pend data"; 

poj

One would think something should be possible with ROLLUP: https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html

Unfortunately I have no mysqld instance handy. It was not hard to do with postgres' GROUPING SETS (but it turned out to be slightly slower than choroba's UNION-approach above).

This isn't a ROLLUP, because you don't want totals. Instead, you could use the UNION ALL, but use a CTE so the subquery is only applied once. That is, use a WITH to SELECT all the rows in the TABLE, but add ROW_NUMBER() OVER (ORDER BY queue_name). To avoid the UNION ALL (which in this case would have little to no point, as the CTE already makes a centralized query) you could wrap the ROW_NUMBER() in a CASE to go no higher than 6.Then a single aggregate query with a GROUP BY on the row number.