Calculate percentage of total HTML columns with Jquery

Asked

Viewed 394 times

1

I have a table that is automatically generated by PHP and manipulated by jquery, only I need to calculate the percentage of the total of each of the numerical lines, what is the best way to do this? 

<div id="tableResults" class="container">
  <table id="table_resultados" class="table table-bordered table-hover">
    <thead class="table-head">
      <tr>
        <th>SEX</th>
        <th class="names">FATAL</th>
        <th class="names">SERIOUS</th>
        <th class="names">OTHERS</th>
        <th class="names">TOTAL</th>
      </tr>
    </thead>
    <tbody>
      <tr>
        <td>WOMAN</td>
        <td class="VitimaFATAL">4</td>
        <td class="SERIOUS">6</td>
        <td class="OTHERS">1</td>
        <td class="Total">11</td>
      </tr>
      <tr>
        <td>MEN</td>
        <td class="FATAL">18</td>
        <td class="SERIOUS">28</td>
        <td class="OTHERS">23</td>
        <td class="Total">69</td>
      </tr>
      <tr>
        <td>UNKNOWN</td>
        <td class="FATAL">3</td>
        <td class="SERIOUS">1679</td>
        <td class="OTHERS">3129</td>
        <td class="Total">4811</td>
      </tr>
      <tr id="Totais">
        <td>TOTAL</td>
        <td id="FATAL">25</td>
        <td id="SERIOUS">1713</td>
        <td id="OTHERS">3153</td>
        <td id="Total">4891</td>
      </tr>
    </tbody>
  </table>
</div>

This table comes from a dynamic search in PHP and MYSQL that generates the values, I already made a function in jQuery to calculate the total 

function colSum() {
    var ids = new Array();
    $('#table_resultados .names').each(function (){   
        ids.push($(this).html().replace(/\s/g, ''));
        $('#Totais').append('<td id="' + $(this).html().replace(/\s/g, '') + '"></td>')
    });

    $.each(ids, function (index, value) {
        var sum = 0;
        $('.' + value).each(function () {
            sum += parseInt($(this).html());
        });

        $('#' + value).html(sum);
    });

I need to calculate the percentage of the total of all columns. In this example I need to insert a column after the FATAL, SERIOUS, OUTHERS % of total and with the values, example:

  • the column % of the total FATAL would be 36,36 - 26,08 - 0,06 - 0,51
  • the column % of the total SERIOUS would be 54,54 - 40,57 - 34,89 - 35,02

The account to be made is the value of the column multiplied by 100 divided by the value of the total of the same example row of the first row 4*100/11

I created an HTML to look more or less like I need, I don’t know how to generate it dynamically for all lines

<div id="tableResults" class="container">
  <table id="table_resultados" class="table table-bordered table-hover">
    <thead class="table-head">
      <tr>
        <th>SEX</th>
        <th class="names">FATAL</th>
        <th>% do TOTAL</th>
        <th class="names">SERIOUS</th>
        <th class="names">OTHERS</th>
        <th class="names">TOTAL</th>
      </tr>
    </thead>
    <tbody>
      <tr>
        <td>WOMAN</td>
        <td class="VitimaFATAL">4</td>
        <th>36,36</th>
        <td class="SERIOUS">6</td>
        <td class="OTHERS">1</td>
        <td class="Total">11</td>
      </tr>
      <tr>
        <td>MEN</td>
        <td class="FATAL">18</td>
        <th>26,08</th>
        <td class="SERIOUS">28</td>
        <td class="OTHERS">23</td>
        <td class="Total">69</td>
      </tr>
      <tr>
        <td>UNKNOWN</td>
        <td class="FATAL">3</td>
        <th>0,06</th>
        <td class="SERIOUS">1679</td>
        <td class="OTHERS">3129</td>
        <td class="Total">4811</td>
      </tr>
      <tr id="Totais">
        <td>TOTAL</td>
        <td id="FATAL">25</td>
        <th>0,51</th>
        <td id="SERIOUS">1713</td>
        <td id="OTHERS">3153</td>
        <td id="Total">4891</td>
      </tr>
    </tbody>
  </table>
</div>

javascript jquery

  • You can’t put the HTML already with the columns % of the total, it’s a bit confusing, at least for me, understand.

  • Explain to me something, where you got these figures? a coluna % do total do FATAL seria 36,36 - 26,08 - 0,06 - 0,51 and a coluna % do total do SERIOUS seria 54,54 - 40,57 - 34,89 - 35,02. I was able to understand that you wanted to take the values that are in the columns and group by the classes: FATAL,SERIOUS,OTHERS;

  • @Marconi I did on the calculator

  • @Guilhermefreire what is the calculation to arrive at these percentages? Could edit your question and add something else?

  • @Marconi The account to be made is the value of the column multiplied by 100 divided by the value of the total of the same row example of the first row 4*100/11 so I do not know how to do this dynamically by jquery on all rows

  • @Guilhermefreire I saw your edition got much better, I’ll have lunch at the moment, so I arrive and do not have an answer help you.

  • @Leocaracciolo do not know what to do with jquery to get these values I already use php q does a lot of stuff with mysql, I thought q would be easier, post your reply in php for me to take a look, maybe it will be simpler even

  • @Leocaracciolo to using Pdo

  • It’s there in the answer in php Pdo

Show 4 more comments

2 answers

2


I will add my answer with the premise that your function to add the total value is correct, so I will use the HTML posted in your reply, already with the total, as basis.

I will not use the class name or id's, that would be simpler. I will do it in a slightly more generic way.

First, I add the percentage column after each column. Together, I save the index() of the column, to know its actual position.

var indexs = new Array();
$('table#table_resultados thead tr th.names').each(function() {
  var coluna = $(this); // Salvo a coluna atual
  indexs.push(coluna.index()); //Salvo o Index
  coluna.after('<th>%</th>'); // Insiro a nova coluna
});

After saving the index's and have already inserted the columns for the percentages, I go through each row to get the column value total. After obtaining the column value total, I go through the index's saved, searching the value of each column and its respective percentage.

After having all this data, I just enter the value in the specific row and column.

$('table#table_resultados tbody tr').each(function() {
  var coluna = $(this); //Salvo a coluna
  //console.log(coluna)
  var total = parseFloat(coluna.find('td').last().html()); // Salvo o valor total da linha
  $(indexs).each(function(key, value) { // Percorro os index's salvos
    var row = coluna.find('td').eq(value); // Salvo a linha 
    var valor = parseFloat(row.html()); // Salvo o valor total da linha
    var procentagem = (valor / total) * 100; // Verifico a porgentagem
    row.after('<td>' + procentagem.toFixed(3) + '</td>'); // Adiciono o valor na linha e na coluna porcentagem
  });
});

Below you can check the functional example.

var indexs = new Array();
$('table#table_resultados thead tr th.names').each(function() {
  var coluna = $(this); // Salvo a coluna atual
  indexs.push(coluna.index()); //Salvo o Index
  coluna.after('<th>%</th>'); // Insiro a nova coluna
});

$('table#table_resultados tbody tr').each(function() {
  var coluna = $(this); //Salvo a coluna
  var total = parseFloat(coluna.find('td').last().html()); // Salvo o valor total da linha
  $(indexs).each(function(key, value) { // Percorro os index's salvos
    var row = coluna.find('td').eq(value); // Salvo a linha 
    var valor = parseFloat(row.html()); // Salvo o valor total da linha
    var procentagem = (valor / total) * 100; // Verifico a porgentagem
    row.after('<td>' + procentagem.toFixed(3) + '</td>'); // Adiciono o valor na linha e na coluna porcentagem
  });
});
th {
  width: 150px;
  text-align: left;
}
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<div id="tableResults" class="container">
  <table id="table_resultados" class="table table-bordered table-hover">
    <thead class="table-head">
      <tr>
        <th>SEX</th>
        <th class="names">FATAL</th>
        <th class="names">SERIOUS</th>
        <th class="names">OTHERS</th>
        <th>TOTAL</th>
      </tr>
    </thead>
    <tbody>
      <tr>
        <td>WOMAN</td>
        <td class="FATAL">4</td>
        <td class="SERIOUS">6</td>
        <td class="OTHERS">1</td>
        <td class="Total">11</td>
      </tr>
      <tr>
        <td>MEN</td>
        <td class="FATAL">18</td>
        <td class="SERIOUS">28</td>
        <td class="OTHERS">23</td>
        <td class="Total">69</td>
      </tr>
      <tr>
        <td>UNKNOWN</td>
        <td class="FATAL">3</td>
        <td class="SERIOUS">1679</td>
        <td class="OTHERS">3129</td>
        <td class="Total">4811</td>
      </tr>
      <tr id="Totais">
        <td>TOTAL</td>
        <td id="FATAL">25</td>
        <td id="SERIOUS">1713</td>
        <td id="OTHERS">3153</td>
        <td id="Total">4891</td>
      </tr>
    </tbody>
  </table>
</div>

  • 1

    Thank you so much for your help, I didn’t know how to pick up the column that way you made it perfect, I learned a lot even now

1

At the request of the questioner as per comment

Calculate percentage of total columns with PHP

As I do not have the CSS file I did in the most trivial way.

$db = new PDO('mysql:host=localhost;dbname=DBname;charset=utf8mb4', 'USUARIO', 'SENHA');


echo "<div align=\"center\" id=\"tableResults\" class=\"container\">
  <table id=\"table_resultados\" width=\"800\" class=\"table table-bordered table-hover\">
    <thead class=\"table-head\">
     <tr>
      <th>SEX</th>
      <th class=\"names\">FATAL</th>
      <th>% do TOTAL</th>
      <th class=\"names\">SERIOUS</th>
      <th>% do TOTAL</th>
      <th class=\"names\">OTHERS</th>
      <th>% do TOTAL</th>
      <th class=\"names\">TOTAL</th>
    </tr>
   </thead>
  <tbody>";

    foreach($db->query('SELECT * FROM NomeTabela') as $row) {

       $nome=$row["nome"];
                
       if ($nome=="WOMAN"){
            $classe="VitimaFATAL";
        }else{
            $classe="FATAL";
        }
                
        $fatal=$row["fatal"];
        $serious=$row["serious"];
        $others=$row["others"];
        $total=$row["fatal"]+$row["serious"]+$row["others"];
                
        echo "<tr>
              <td align=\"center\" width=\"150\">".$row["nome"]."</td>
              <td align=\"center\" width=\"50\" class=\"".$classe."\">".$fatal."</td>
              <th align=\"center\" width=\"150\">".number_format(($fatal*100)/$total,2, '.', '')."</th>
              <td align=\"center\" width=\"50\" class=\"SERIOUS\">".$serious."</td>
              <th align=\"center\" width=\"150\">".number_format(($serious*100)/$total,2, '.', '')."</th>
              <td align=\"center\" width=\"50\" class=\"OTHERS\">".$others."</td>
              <th align=\"center\" width=\"150\">".number_format(($others*100)/$total,2, '.', '')."</th>
              <td align=\"center\" width=\"50\" class=\"Total\">".$total."</td>
              </tr>";
        }


  echo "</tbody>
 </table>
</div>";

Browser other questions tagged javascript jquery

You are not signed in. Login or sign up in order to post.