How To Add A Column Sum Feature To My Datatable?
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 search
Solution 1:
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)constnum2curr = num => '$'+num.toFixed(2).replace(/\d(?=(\d{3})+\.)/g, '$&,');
//datatables initializationconst dataTable = $('#example').DataTable({
dom: 't',
//append individual filter inputsinitComplete: 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#totalsalarydrawCallback: 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><linktype="text/css"href="https://cdn.datatables.net/v/dt/jq-3.3.1/dt-1.10.18/rg-1.1.0/datatables.min.css" /><scripttype="application/javascript"src="https://code.jquery.com/jquery-3.3.1.min.js"></script><scripttype="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><scriptsrc="test.js"></script></head><body><tableid="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><spanid="totalsalary"></span></body></html>Solution 2:
Use the code below for summation
jQuery.fn.dataTable.Api.register( 'sum()', function () {
returnthis.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.
Post a Comment for "How To Add A Column Sum Feature To My Datatable?"