Totaling Table Columns

Many of the applications I deal with are financial in nature, and often have a requirement to provide subtotals and totals of column rows. This can be a daunting exercise if attacked from a DOM perspective, but it's pretty easy with jQuery. Take a look at this table:














We want to be able to total the stores' sales figures for each region, then provide a grand total up a the top. To make this easy, I assigned an ID to the table, and a class to reach row depending on its type. Here's the table code:

<table id="myTable" border="1">
<thead>
<tr class="headerRow">
 <th>Region</th>
 <th>Sales</th>
</tr>
</thead>
<tbody>
<tr class="totalsRow">
 <td>All</td>
 <td>0</td>
</tr>
<tr class="regionRow">
 <td>North</td>
 <td>0</td>
</tr>
<tr class="storeRow">
 <td>  Boston</td>
 <td>10</td>
</tr>
<tr class="storeRow">
 <td>  New York</td>
 <td>20</td>
</tr>
<tr class="regionRow">
 <td>South</td>
 <td>0</td>
</tr>
<tr class="storeRow">
 <td>  Charleston</td>
 <td>40</td>
</tr>
<tr class="storeRow">
 <td>  Atlanta</td>
 <td>80</td>
</tr>
</tbody>
</table>

You can see the classes defined for totalsRow, regionRow, and storeRow. Now all we need is a little plumbing to figure out the totals. Here's a function I wrote that will work with any table having figures in columns (that is, no additional markup inside the tags):

<script type="text/javascript">
  /*
    totalTableSubRows()
    By Jeff Peters, Aug 2008.  Released to the public domain.
    Courtesy of jQuery Nuts and Bolts, http:// jquerynab.blogspot.com
  */
  function totalTableSubRows(tableID, totalClass, dataClass, columnNumber) {
    tableSelector = "#" + tableID;
    totalSelector = "." + totalClass;
    dataSelector = "." + dataClass;
    columnIndex = columnNumber - 1;
    $(tableSelector + ' tr' + totalSelector).each(function(){
      $(this).children("td:eq(" + columnIndex + ")").html(0);
      total = 0;
      $(this).nextAll().each(function(){
        if($(this).hasClass(dataClass)){
          thisVal = $(this).children("td:eq(" + columnIndex + ")").html() * 1;
          if (!isNaN(thisVal)) {
            total += thisVal;
          }
        } else {
          if($(this).hasClass(totalClass)){
            return false;
          }
        }
      })
      $(this).children("td:eq(" + columnIndex + ")").html(total);
    })
  }

  $(document).ready(function(){
    $("#btnCalculate").click(function(){
      totalTableSubRows("myTable","regionRow", "storeRow",2);
      totalTableSubRows("myTable","totalsRow", "storeRow",2);
    });
  });
</script>

To make it work , you just call it with the desired parameters. Since we want to sets of calculations--one for the region rows, and one for the totals row, we call the function twice:

totalTableSubRows("myTable","regionRow", "storeRow",2);
totalTableSubRows("myTable","totalsRow", "storeRow",2);

The first call goes to myTable, calculates the totals from the storeRow rows under each regionRow row, and returns the total for each regionRow row to that row, with numbers found in the second column. The second call does the same thing, but totals all regionRow rows under the totalsRow. All done.

This function is extremely useful if you're injecting rows of data into the table. You can run the function right after a row injection (or deletion), and see the totals stay current.

As soon as I can figure out how to get blogspot to allow demo code pages, I'll post this so you can watch a live demo here.