Calculate Sum (Total) of DataTables Column using Footer Callback
by Vincy. Last modified on July 5th, 2023.
DataTables provides callback functions to manipulate header and footer HTML data and add value to the presentation. Using these callback functions, we can modify 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 the total sum for a column, and more.
In this tutorial, I will present an example code to calculate a column total and display it 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 the 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 initialized with the DataTables library to show database results. We set the serverSide option as valid to get the database result using an AJAX call. This code contains table footer markup with empty container tags.
After executing footer callback functions, these containers will be filled with the sum of the column data.
<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 a footer callback function to calculate the sum of the column data. This function calculates the sum or the total per column basis.
This function gets the value by using column(index).data() and converts it into an integer format to calculate the total. After calculating the column total, it uses the column(index).html() to include the total in the footer dynamically.
<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.
ma’am tell me laravel8 like this.
Sure, I will try to write on Laravel soon.
Nice..
Thanks Roman.
we can also use loop and array
“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
for (let i=1; i<=17; i++)
{
const total=[];
total[i] = api
.column(i)
.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(i).footer()).html(total[i]);
}
},