Search code examples
javascripthtmljquerydatatablessum

Update the sum when making a filter in datatables

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 class="text-center">
        <div class="row  mt-1">
            <div class="col-6">
                <div class="card text-center">
                    <div class="card-header p-1">
                        ACOUNT
                    </div>
                    <div class="card-body p-1" style="background: #76b8da">
                        <h5>S/ <span id="tblProfit"></span></h5>
                    </div>
                </div>
            </div>
            <div class="col-6">
                <div class="card text-center">
                    <div class="card-header p-1">
                        <div class="row">
                            <div class="col-sm-8">
                               TOTAL
                            </div>
                        </div>
                    </div>
                    <div class="card-body p-1" style="background: #4ef3ae">
                    <h5>S/ <span id="sumados"></span></h5>
                    </div>
                </div>
            </div>
        </div>  
    </div>
    <div class="card card-body">
        <div class="row mb-3 mt-2">
            <div class="col-2">
                <span><strong>User</strong></span>
                <select data-column="0" class="form-control filter-select">
                    <option value="">Todos</option>
                    <option value="Jose">Jose</option>
                    <option value="Carlos">Carlos</option>
                </select>
            </div>
        </div>
    </div>        
    <table id="table_id" class="display">
        <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

Solution

  • 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 class="text-center">
        <div class="row  mt-1">
            <div class="col-6">
                <div class="card text-center">
                    <div class="card-header p-1">
                        ACOUNT
                    </div>
                    <div class="card-body p-1" style="background: #76b8da">
                        <h5>S/ <span id="tblProfit"></span></h5>
                    </div>
                </div>
            </div>
            <div class="col-6">
                <div class="card text-center">
                    <div class="card-header p-1">
                        <div class="row">
                            <div class="col-sm-8">
                               TOTAL
                            </div>
                        </div>
                    </div>
                    <div class="card-body p-1" style="background: #4ef3ae">
                    <h5>S/ <span id="sumados"></span></h5>
                    </div>
                </div>
            </div>
        </div>  
    </div>
    <div class="card card-body">
        <div class="row mb-3 mt-2">
            <div class="col-2">
                <span><strong>User</strong></span>
                <select data-column="0" class="form-control filter-select">
                    <option value="">Todos</option>
                    <option value="Jose">Jose</option>
                    <option value="Carlos">Carlos</option>
                </select>
            </div>
        </div>
    </div>        
    <table id="table_id" class="display">
        <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>