Calculate Sum (Total) of DataTables Column using Footer Callback
by Vincy. Last modified on July 13th, 2022.
DataTables provides callback functions to manipulate header and footer HTML data and add value to the presentation. Using these callback functions we can do various modifications to the resultant table dynamically.
We can add information that adds value to the header and footer portion of the DataTables. For example, we can add column specific header information, show total sum for a column and more.
In this tutorial, I will present you an example code which will calculate a column total and display it by using DataTables footer callback functions. Those callback functions to modify the header and footer are headerCallback and footerCallback given as options in the DataTables plugin.
In a previous tutorial, we have seen how to list database results using DataTables by enabling server-side processing have a look at it to learn about server-side processing of DataTables.
The following screenshot shows the list of database results by enabling server-side processing. It shows the column total computed by using the DataTables footer callback script.
HTML Code for DataTables with Column Total
The following code shows the table HTML which is initialized with the DataTables library to show database results. We set the serverSide option as true to get the database result by using AJAX call. This code contains table footer markup with empty container tags.
These containers will be filled with the sum of the column data after executing footer callback functions.
<head>
<title>Calculate Sum (Total) of DataTables Column using Footer Callback</title>
<link rel="stylesheet" href="vendor/DataTables/datatables.min.css">
<link rel="stylesheet" href="style.css">
<script src="vendor/jquery/jquery-1.11.2.min.js" type="text/javascript"></script>
<script src="vendor/DataTables/datatables.min.js" type="text/javascript"></script>
</head>
<body>
<table id="tbl-attendance" class="display nowrap" cellspacing="0" width="100%">
<thead>
<tr>
<th>Subject</th>
<th>Mon</th>
<th>Tue</th>
<th>Wed</th>
<th>Thu</th>
<th>Fri</th>
</tr>
</thead>
<tfoot align="right">
<tr><th></th><th></th><th></th><th></th><th></th><th></th></tr>
</tfoot>
</table>
</body>
Footer Callback Script
The following script is used to initialize the DataTables library with the reference of the HTML table id selector. It contains footer callback function to calculate the sum of the column data. In this function, the sum or the total is calculated per column basis.
In this function, it gets the value by using column(index).data() and convert it into an integer format to calculate the total. After calculating column total, it uses the column(index).html() to dynamically include the total to the footer.
<script>
$(document).ready(function() {
$('#tbl-attendance').dataTable({
"footerCallback": function ( row, data, start, end, display ) {
var api = this.api(), data;
// converting to interger to find total
var intVal = function ( i ) {
return typeof i === 'string' ?
i.replace(/[\$,]/g, '')*1 :
typeof i === 'number' ?
i : 0;
};
// computing column Total of the complete result
var monTotal = api
.column( 1 )
.data()
.reduce( function (a, b) {
return intVal(a) + intVal(b);
}, 0 );
var tueTotal = api
.column( 2 )
.data()
.reduce( function (a, b) {
return intVal(a) + intVal(b);
}, 0 );
var wedTotal = api
.column( 3 )
.data()
.reduce( function (a, b) {
return intVal(a) + intVal(b);
}, 0 );
var thuTotal = api
.column( 4 )
.data()
.reduce( function (a, b) {
return intVal(a) + intVal(b);
}, 0 );
var friTotal = api
.column( 5 )
.data()
.reduce( function (a, b) {
return intVal(a) + intVal(b);
}, 0 );
// Update footer by showing the total with the reference of the column index
$( api.column( 0 ).footer() ).html('Total');
$( api.column( 1 ).footer() ).html(monTotal);
$( api.column( 2 ).footer() ).html(tueTotal);
$( api.column( 3 ).footer() ).html(wedTotal);
$( api.column( 4 ).footer() ).html(thuTotal);
$( api.column( 5 ).footer() ).html(friTotal);
},
"processing": true,
"serverSide": true,
"ajax": "server.php"
} );
} );
</script>
Comments to “Calculate Sum (Total) of DataTables Column using Footer Callback”
Leave a Reply
Hi, I’m Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I’m currently available for freelance work.
nice job girl!
Thank you Dean.