How to add a column SUM feature to my datatable?

Question

I have created a datatable which has a multi search feature in the footer and I would like to have a feature which adds up all the salaries of what is currently displayed or searched. I am not sure how to do that.

Here is my HTML code for the table

    <script src="https://code.jquery.com/jquery-3.3.1.js"></script>
    <script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
    <script src="./search.js"></script>
    <link href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">

   <table id="example" class="display" style="width:100%">
            <thead>
                <tr>
                    <th>Name</th>
                    <th>Position</th>
                    <th>Office</th>
                    <th>Age</th>
                    <th>Start date</th>
                    <th>Salary</th>
                </tr>
            </thead>
            <tbody>
                <tr>
                    <td>Tiger Nixon</td>
                    <td>System Architect</td>
                    <td>Edinburgh</td>
                    <td>61</td>
                    <td>2011/04/25</td>
                    <td>$320,800</td>
                </tr>
                <tr>
                    <td>Garrett Winters</td>
                    <td>Accountant</td>
                    <td>Tokyo</td>
                    <td>63</td>
                    <td>2011/07/25</td>
                    <td>$170,750</td>
                </tr>
                <tr>
                    <td>Ashton Cox</td>
                    <td>Junior Technical Author</td>
                    <td>San Francisco</td>
                    <td>66</td>
                    <td>2009/01/12</td>
                    <td>$86,000</td>
                </tr>
                <tr>
                    <td>Cedric Kelly</td>
                    <td>Senior Javascript Developer</td>
                    <td>Edinburgh</td>
                    <td>22</td>
                    <td>2012/03/29</td>
                    <td>$433,060</td>
                </tr>
                <tr>
                    <td>Airi Satou</td>
                    <td>Accountant</td>
                    <td>Tokyo</td>
                    <td>33</td>
                    <td>2008/11/28</td>
                    <td>$162,700</td>
                </tr>
                <tr>
                    <td>Brielle Williamson</td>
                    <td>Integration Specialist</td>
                    <td>New York</td>
                    <td>61</td>
                    <td>2012/12/02</td>
                    <td>$372,000</td>
                </tr>
                <tr>
                    <td>Herrod Chandler</td>
                    <td>Sales Assistant</td>
                    <td>San Francisco</td>
                    <td>59</td>
                    <td>2012/08/06</td>
                    <td>$137,500</td>
                </tr>
                <tr>
                    <td>Rhona Davidson</td>
                    <td>Integration Specialist</td>
                    <td>Tokyo</td>
                    <td>55</td>
                    <td>2010/10/14</td>
                    <td>$327,900</td>
                </tr>
               </tbody>
            <tfoot>
                <tr>
                    <th>Name</th>
                    <th>Position</th>
                    <th>Office</th>
                    <th>Age</th>
                    <th>Start date</th>
                    <th>Salary</th>
                </tr>
            </tfoot>
        </table>

Here is my search.js Code

 $(document).ready(function() {
    // Setup - add a text input to each footer cell
    $('#example tfoot th').each( function () {
        var title = $(this).text();
        $(this).html( '<input type="text" placeholder="Search '+title+'" />' );
    } );

    // DataTable
    var table = $('#example').DataTable();

    // Apply the search
    table.columns().every( function () {
        var that = this;

        $( 'input', this.footer() ).on( 'keyup change', function () {
            if ( that.search() !== this.value ) {
                that
                    .search( this.value )
                    .draw();
            }
        } );
    } );
  } );

I am trying to add a sum feature to this code where it adds up the total of the salary column, can someone please help me? im not sure how to do this

This example: datatables.net/examples/advanced_init/footer_callback.html should provide most of what you need. The following is related but gets into the specifics of handling the SUM when a filter is in effect: datatables.net/forums/discussion/45069/… — David Tansey
– David Tansey, Commented Jul 17, 2019 at 21:32

Yevhen Horbunkov · Accepted Answer · 2019-07-19 15:02:31Z

That topic has been raised multiple times over here at SO, you may inquiry my earlier post in that regard.

The trick here is to use selector-modifier {search:'applied'} together with methods .column() (if you need to summarize single column) or .rows() for multi-column totals.

Another helpful method .data() may be used to extract the data into array (1, 2).

In order to refresh your totals upon each table re-draw, you may employ drawCallback option, to specify callback function that re-calculates totals for visible rows and puts the result into desired node.

Check out following live demo of that approach:

//format number as currency (not essential within current context)
const num2curr = num => '$'+num.toFixed(2).replace(/\d(?=(\d{3})+\.)/g, '$&,');
//datatables initialization
const dataTable = $('#example').DataTable({
  dom: 't',
  //append individual filter inputs
  initComplete: function(){
    this.api().columns().every(function(){
      $(this.footer()).html(`<input colindex="${this.index()}" placeholder="${$(this.header()).text()}"></input>`)
    })
  },
   //calculate total salary and put that into span#totalsalary
   drawCallback: function(){
    const totalSalary = this
      .api()
      .column(5,{search:'applied'})
      .data()
      .toArray()
      //remove '$',',', keep decimal separator '.', summarize
      .reduce((total,salary) => total+=Number(salary.replace(/[^0-9\.]/g,'')),0);
    //insert result into the <span> text
    $('#totalsalary').text(`Total salary for filtered rows is: ${num2curr(totalSalary)}`);
   }
});
//individual filtering
$('#example').on('keyup', 'tfoot input', function(){
  dataTable.column($(this).attr('colindex')).search($(this).val()).draw()
});

<!doctype html><html><head><link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/jq-3.3.1/dt-1.10.18/rg-1.1.0/datatables.min.css" /><script type="application/javascript" src="https://code.jquery.com/jquery-3.3.1.min.js"></script><script type="text/javascript" src="https://cdn.datatables.net/v/dt/jq-3.3.1/dt-1.10.18/rg-1.1.0/datatables.min.js"></script><script src="test.js"></script></head><body><table id="example" class="display" style="width:100%"><thead><tr><th>Name</th><th>Position</th><th>Office</th><th>Age</th><th>Start date</th><th>Salary</th></tr></thead><tbody><tr><td>Tiger Nixon</td><td>System Architect</td><td>Edinburgh</td><td>61</td><td>2011/04/25</td><td>$320,800</td></tr><tr><td>Garrett Winters</td><td>Accountant</td><td>Tokyo</td><td>63</td><td>2011/07/25</td><td>$170,750</td></tr><tr><td>Ashton Cox</td><td>Junior Technical Author</td><td>San Francisco</td><td>66</td><td>2009/01/12</td><td>$86,000</td></tr><tr><td>Cedric Kelly</td><td>Senior Javascript Developer</td><td>Edinburgh</td><td>22</td><td>2012/03/29</td><td>$433,060</td></tr><tr><td>Airi Satou</td><td>Accountant</td><td>Tokyo</td><td>33</td><td>2008/11/28</td><td>$162,700</td></tr><tr><td>Brielle Williamson</td><td>Integration Specialist</td><td>New York</td><td>61</td><td>2012/12/02</td><td>$372,000</td></tr><tr><td>Herrod Chandler</td><td>Sales Assistant</td><td>San Francisco</td><td>59</td><td>2012/08/06</td><td>$137,500</td></tr><tr><td>Rhona Davidson</td><td>Integration Specialist</td><td>Tokyo</td><td>55</td><td>2010/10/14</td><td>$327,900</td></tr></tbody><tfoot><tr><th>Name</th><th>Position</th><th>Office</th><th>Age</th><th>Start date</th><th>Salary</th></tr></tfoot></table><span id="totalsalary"></span></body></html>

You may delete your above comments. They're not essential in the context of your initial question.

David Tansey · Accepted Answer · 2019-07-18 15:25:27Z

1

Use the code below for summation

jQuery.fn.dataTable.Api.register( 'sum()', function ( ) {
return this.flatten().reduce( function ( a, b ) {
    if ( typeof a === 'string' ) {
        a = a.replace(/[^\d.-]/g, '') * 1;
    }
    if ( typeof b === 'string' ) {
        b = b.replace(/[^\d.-]/g, '') * 1;
    }

    return a + b;
}, 0 );
} );

Then, you can get the sum after you draw with

that.column(5, {"filter": "applied"}).data().sum();

Also, omit the comma and $. You can refer to here for more information.

edited Jul 18, 2019 at 15:25

David Tansey

6,0104 gold badges39 silver badges54 bronze badges

answered Jul 18, 2019 at 5:17

Roya

3422 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to add a column SUM feature to my datatable?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related