In this example let’s check how to calculate sum of datatable columns (multiple columns) and display the same in footer.
In some cases we may need to display the sum of datatable columns at the footer, for example total marks. We can display the whole total – means the sum of datatable columns without pagination or current page – sum of datatable columns with pagination. Let’s check how to calculate sum of datatable columns – multiple columns using jQuery datatable plugin sum().
CSS :-
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.5/css/jquery.dataTables.css">jQuery :-
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/plug-ins/1.11.5/api/sum().js"></script>We will be using the additional jQuery Datatable plugin called sum().js. By using this it is very easy to calculate the sum of datatable column and display in footer with heading.
HTML :-
<!DOCTYPE html>
<html>
<head>
<title>Datatable</title>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.5/css/jquery.dataTables.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/plug-ins/1.11.5/api/sum().js"></script>
</head>
<body style="padding: 100px;">
<table id="table_id" class="display">
<thead>
<tr>
<th>#</th>
<th>Name</th>
<th>English</th>
<th>Maths</th>
<th>Science</th>
<th>IT</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td>Airi Satou</td>
<td>1035</td>
<td>47</td>
<td>50</td>
<td>45</td>
</tr>
<tr>
<td>2</td>
<td>Angelica Ramos</td>
<td>46</td>
<td>48</td>
<td>45</td>
<td>50</td>
</tr>
<tr>
<td>3</td>
<td>Ashton Cox</td>
<td>36</td>
<td>38</td>
<td>40</td>
<td>50</td>
</tr>
<tr>
<td>4</td>
<td>Bradley Greer</td>
<td>35</td>
<td>47</td>
<td>50</td>
<td>45</td>
</tr>
<tr>
<td>5</td>
<td>Brenden Wagner</td>
<td>29</td>
<td>40</td>
<td>38</td>
<td>35</td>
</tr>
<tr>
<td>6</td>
<td>Brielle Williamson</td>
<td>46</td>
<td>48</td>
<td>45</td>
<td>50</td>
</tr>
<tr>
<td>7</td>
<td>Bruno Nash</td>
<td>28</td>
<td>33</td>
<td>30</td>
<td>39</td>
</tr>
<tr>
<td>8</td>
<td>Caesar Vance</td>
<td>35</td>
<td>47</td>
<td>50</td>
<td>45</td>
</tr>
<tr>
<td>9</td>
<td>Cara Stevens</td>
<td>29</td>
<td>40</td>
<td>38</td>
<td>35</td>
</tr>
<tr>
<td>10</td>
<td>Cedric Kelly</td>
<td>46</td>
<td>48</td>
<td>45</td>
<td>50</td>
</tr>
<tr>
<td>11</td>
<td>Dai Rios</td>
<td>36</td>
<td>38</td>
<td>40</td>
<td>50</td>
</tr>
<tr>
<td>12</td>
<td>Fiona Green</td>
<td>36</td>
<td>38</td>
<td>40</td>
<td>50</td>
</tr>
<tr>
<td>13</td>
<td>Gavin Cortez</td>
<td>35</td>
<td>47</td>
<td>50</td>
<td>45</td>
</tr>
</tbody>
<tfoot>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
<th></th>
</tfoot>
</table>
</body>
<script type="text/javascript">
$(document).ready( function () {
$('#table_id').DataTable( {
drawCallback: function () {
var api = this.api();
var sum = 0;
var formated = 0;
//to show first th
$(api.column(0).footer()).html('Total');
for(var i=2; i<=5;i++)
{
sum = api.column(i, {page:'current'}).data().sum();
//to format this sum
formated = parseFloat(sum).toLocaleString(undefined, {minimumFractionDigits:2});
$(api.column(i).footer()).html('$'+formated);
}
}
});
});
</script>
</html>Script :- Explanation
<script type="text/javascript">
$(document).ready( function () {
$('#table_id').DataTable( {
drawCallback: function () {
var api = this.api();
var sum = 0;
var formated = 0;
//to show first th
$(api.column(0).footer()).html('Total');
for(var i=2; i<=5;i++)
{
sum = api.column(i, {page:'current'}).data().sum();
//to format this sum
formated = parseFloat(sum).toLocaleString(undefined, {minimumFractionDigits:2});
$(api.column(i).footer()).html('$'+formated);
}
}
});
});
</script>- Here, datatable initialized by calling DataTable() on table id table_id.
- A built-in function drawCallback() is called inside DataTable()
- this.api() – this returns an API instance as this is the table node
- $(api.column(0).footer()).html(‘Total’); – this is to display heading Total in footer, it refers to the 0th column – api.column(0)
- I given a loop form 2-5 as I need sum of columns from 2nd position to 5th position
- api.column(i).data().sum(); – this will give the sum of all values in the i^th column – remember this will give you the sum of whole rows for example there are 1000 rows it will give sum of 1000 rows. If you need sum of the current pagination use {page:’current’}
Syntax – api.column(i, {page:’current’}).data().sum(); - parseFloat(sum).toLocaleString(undefined, {minimumFractionDigits:2}); – I have formatted the result with comma(,) and .00 – if you do not need avoid this line and just use sum
- $(api.column(i).footer()).html(‘$’+formated); – this line will attach the sum in footer to the respective columns( i^ th column).
Hope this example helps you to calculate sum of datatable columns easily.
If you want to create a simple datatable please Click me.
To know more about datatable sum click here.
Brilliant. Exactly what I needed and so easy to implement. Thanks very much
Thank you
Uncaught TypeError: api.column(…).data(…).sum is not a function
$(document).ready(function() { var table = $(‘#registros’).DataTable( {
“language”: {
“url”: “//cdn.datatables.net/plug-ins/1.10.15/i18n/Spanish.json”
},
rowReorder: { selector: ‘td:nth-child(2)’ }, “order”: [[ 3, “asc” ]],
responsive: true,
dom: ‘Bfrtip’,
buttons: [‘excel’, ‘pdf’, ‘print’],
“lengthMenu”: [[“999999”], [“999999”]],
drawCallback: function () {
var api = this.api();
var sum = 0;
var formated = 0;
//to show first th
$(api.column(0).footer()).html(‘Total’);
for(var i=2; i<=5;i++)
{
sum = api.column(i, {page:"current"}).data().sum();
//to format this sum
formated = parseFloat(sum).toLocaleString(undefined, {minimumFractionDigits:2});
$(api.column(i).footer()).html('$'+formated);
}
}
}
);
});
=(