perlquestion
perlanswers
<c>
+-----------------+--------------+--------------+
| 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 |
| 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 |
+-----------------+--------------+--------------+
</c>
<br>
I had created the following sub routine to display the values.
<br>
<c>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_pend,jobs_run from queues;";
my $sth = $DBH->prepare( $stmt );
$sth->execute() or print "Could not in7sert_run_pend data";
my $tmp = 0;
while(my @row_array=$sth->fetchrow_array)
{
if ($tmp == 0) {
$var_data_running .= "\[\"$row_array[0] \($row_array[2]\)\",$row_array[2]\]";
$var_data_pending .= "\[\"$row_array[0] \($row_array[1]\)\",$row_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();
}
</c>
<br>
Now my query is i want to take maximum of 5 from the above table and the remaining table values should be sum it added with new row name called others.So i had tried the following script
<br>
<c>
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_pend,jobs_run from queues order by queue_name limit 5;";
my $sth = $DBH->prepare( $stmt );
$sth->execute() or print "Could not in7sert_run_pend data";
my $tmp = 0;
while(my @row_array=$sth->fetchrow_array)
{
if ($tmp == 0) {
$var_data_running .= "\[\"$row_array[0] \($row_array[2]\)\",$row_array[2]\]";
$var_data_pending .= "\[\"$row_array[0] \($row_array[1]\)\",$row_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();
}
</c>
So the result of the above modified sub routine will be as follows<br>
<c>
+---------------+--------------+--------------+
| 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 |
+---------------+--------------+--------------+
</c>
So now the remaining rows are missing from the table .Those rows should be summed and appended with the name called others
<br>
Expected output:
<c>
+---------------+--------------+--------------+
| 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 |
| others | 212 | 218 |
+---------------+--------------+--------------+
</c>