I need your help please, I am using Datatables where I have already managed to do the sums of my two columns (ACOUNT and TOTAL) but when I make a filter by user I would like the sum to be updated and only show the summed data of that user. for this project I am using the library Sum() from datatables and for the Bootstrap styles.
$(document).ready(function(){
var table = $('#table_id').DataTable({
processing: true,
ordering: true,
"pageLength": 50,
"lengthMenu": [[5,10,50,-1], [5,10,50,"All"]],
"order": [[ 1, "asc" ]],
"autoWidth": false,
"language":{
"url": "//cdn.datatables.net/plug-ins/1.10.21/i18n/Spanish.json"
}
});
//SUMA
//ACOUNT
var arrSalePriceOne = table.column(1).data().sum();
$("#tblProfit").text(( arrSalePriceOne).toLocaleString('en-US'));
//TOTAL
var arrSalePriceTwo = table.column(2).data().sum();
$("#sumados").text(( arrSalePriceTwo).toLocaleString('en-US'));
// dropdown
$('.filter-select').change(function(){
table.column($(this).data('column'))
.search($(this).val())
.draw();
});
});<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<script type="text/javascript" src="https://code.jquery.com/jquery-3.1.0.min.js"></script>
<link rel="stylesheet" type="text/css" href="//cdn.datatables.net/1.10.12/css/jquery.dataTables.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css">
</head>
<body>
<div >
<div >
<div >
<div >
<div >
ACOUNT
</div>
<div style="background: #76b8da">
<h5>S/ <span id="tblProfit"></span></h5>
</div>
</div>
</div>
<div >
<div >
<div >
<div >
<div >
TOTAL
</div>
</div>
</div>
<div style="background: #4ef3ae">
<h5>S/ <span id="sumados"></span></h5>
</div>
</div>
</div>
</div>
</div>
<div >
<div >
<div >
<span><strong>User</strong></span>
<select data-column="0" >
<option value="">Todos</option>
<option value="Jose">Jose</option>
<option value="Carlos">Carlos</option>
</select>
</div>
</div>
</div>
<table id="table_id" >
<thead>
<tr>
<th>User</th>
<th>Account</th>
<th>Total</th>
</tr>
</thead>
<tbody>
<tr>
<td>Carlos</td>
<td>100</td>
<td>223.24</td>
</tr>
<tr>
<td>Carlos</td>
<td>100</td>
<td>321</td>
</tr>
<tr>
<td>Carlos</td>
<td>100</td>
<td>321</td>
</tr>
<tr>
<td>Jose</td>
<td>200</td>
<td>221</td>
</tr>
<tr>
<td>Jose</td>
<td>10</td>
<td>100</td>
</tr>
<tr>
<td>Carlos</td>
<td>30</td>
<td>100</td>
</tr>
<tr>
<td>Jose</td>
<td>50</td>
<td>100</td>
</tr>
<tr>
<td>Carlos</td>
<td>14</td>
<td>100</td>
</tr>
<tr>
<td>Carlos</td>
<td>26</td>
<td>100</td>
</tr>
<tr>
<td>Jose</td>
<td>76</td>
<td>24</td>
</tr>
<tr>
<td>Jose</td>
<td>86</td>
<td>100</td>
</tr>
<tr>
<td>Jose</td>
<td>12</td>
<td>120</td>
</tr>
</tbody>
</table>
<script type="text/javascript" charset="utf8" src="//cdn.datatables.net/1.10.12/js/jquery.dataTables.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/umd/popper.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/plug-ins/1.11.3/api/sum().js"></script>
</body>
</html>I would appreciate your help
CodePudding user response：
You can use the DataTables {search:'applied'} selector modifier to ensure you only sum the filtered column values.
Here is a revised dropdown change event showing this:
// dropdown
$('.filter-select').change(function(){
table.column($(this).data('column'))
.search($(this).val())
.draw();
var account = table.columns( 1, {search:'applied'} ).data().sum();
$("#tblProfit").text(( account).toLocaleString('en-US'));
var total = table.columns( 2, {search:'applied'} ).data().sum();
$("#sumados").text(( total).toLocaleString('en-US'));
});
Update: here is a runnable snippet. It displays the grand totals (no filtering) when the table is first displayed:
$(document).ready(function(){
var table = $('#table_id').DataTable({
processing: true,
ordering: true,
"pageLength": 50,
"lengthMenu": [[5,10,50,-1], [5,10,50,"All"]],
"order": [[ 1, "asc" ]],
"autoWidth": false,
"language":{
"url": "https://cdn.datatables.net/plug-ins/1.10.21/i18n/Spanish.json"
}
});
//SUMA
//ACOUNT
var arrSalePriceOne = table.column(1).data().sum();
$("#tblProfit").text(( arrSalePriceOne).toLocaleString('en-US'));
//TOTAL
var arrSalePriceTwo = table.column(2).data().sum();
$("#sumados").text(( arrSalePriceTwo).toLocaleString('en-US'));
// dropdown
$('.filter-select').change(function(){
table.column($(this).data('column'))
.search($(this).val())
.draw();
var account = table.columns( 1, {search:'applied'} ).data().sum();
$("#tblProfit").text(( account).toLocaleString('en-US'));
var total = table.columns( 2, {search:'applied'} ).data().sum();
$("#sumados").text(( total).toLocaleString('en-US'));
});
});<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
<script type="text/javascript" src="https://code.jquery.com/jquery-3.1.0.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css">
<script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.12/js/jquery.dataTables.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/umd/popper.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.min.js"></script>
<script src="https://cdn.datatables.net/plug-ins/1.11.3/api/sum().js"></script>
</head>
<body>
<div >
<div >
<div >
<div >
<div >
ACOUNT
</div>
<div style="background: #76b8da">
<h5>S/ <span id="tblProfit"></span></h5>
</div>
</div>
</div>
<div >
<div >
<div >
<div >
<div >
TOTAL
</div>
</div>
</div>
<div style="background: #4ef3ae">
<h5>S/ <span id="sumados"></span></h5>
</div>
</div>
</div>
</div>
</div>
<div >
<div >
<div >
<span><strong>User</strong></span>
<select data-column="0" >
<option value="">Todos</option>
<option value="Jose">Jose</option>
<option value="Carlos">Carlos</option>
</select>
</div>
</div>
</div>
<table id="table_id" >
<thead>
<tr>
<th>User</th>
<th>Account</th>
<th>Total</th>
</tr>
</thead>
<tbody>
<tr>
<td>Carlos</td>
<td>100</td>
<td>223.24</td>
</tr>
<tr>
<td>Carlos</td>
<td>100</td>
<td>321</td>
</tr>
<tr>
<td>Carlos</td>
<td>100</td>
<td>321</td>
</tr>
<tr>
<td>Jose</td>
<td>200</td>
<td>221</td>
</tr>
<tr>
<td>Jose</td>
<td>10</td>
<td>100</td>
</tr>
<tr>
<td>Carlos</td>
<td>30</td>
<td>100</td>
</tr>
<tr>
<td>Jose</td>
<td>50</td>
<td>100</td>
</tr>
<tr>
<td>Carlos</td>
<td>14</td>
<td>100</td>
</tr>
<tr>
<td>Carlos</td>
<td>26</td>
<td>100</td>
</tr>
<tr>
<td>Jose</td>
<td>76</td>
<td>24</td>
</tr>
<tr>
<td>Jose</td>
<td>86</td>
<td>100</td>
</tr>
<tr>
<td>Jose</td>
<td>12</td>
<td>120</td>
</tr>
</tbody>
</table>
</body>
</html>