jquery datatables - sum columns within a group

Question

I'm soooo close to getting exactly what I want, just need a little help.

I have grouping working. Now I want to sum the columns for each group and display the total in the group header. Here's a jsfiddle of it since it easier to show what I'm trying to do:

http://jsfiddle.net/RgKPZ/123/

The relevant code:

$(function() {
            oTable = $('#job_history').dataTable({

                "aoColumnDefs": [
                    { "bVisible": false, "aTargets": [ 4,5,6 ] },
                ],
                "aLengthMenu": [[10, 25, 50, -1], ["Show 10 entries", "Show 25 entries", "Show 50 entries", "Show all entries"]], // options in the show rows selector
                "iDisplayLength" : -1, // show all rows by default
                "aaSortingFixed": [[ 5, 'asc' ]],
                "aaSorting": [[ 5, 'asc' ]],
                "bJQueryUI": true,
                "sDom": '<flip>', // filter, length, info, pagination

                "oLanguage": {
                    "sSearch": "", // label for search field - see function below for setting placeholder text
                    "sLengthMenu": "_MENU_", // label for show selector { "sLengthMenu": "Display _MENU_ jobs" }
                    "sInfo": "Showing _START_ to _END_ of _TOTAL_ entries.", // string for information display
                    "sInfoEmpty": "No entries to show", // what to show when info is empty
                    "sInfoFiltered": " (Filtering from _MAX_ entries.)",
                    "sEmptyTable": "There are no entries matching the search criteria.", // shown when table is empty, regardless of filtering
                    "sZeroRecords": "", // shown when nothing is left after filtering
                },

                "fnDrawCallback": function ( oSettings ) {
                    if ( oSettings.aiDisplay.length == 0 )
                    {
                        return;
                    }

                    var nTrs = $('#job_history tbody tr'); // get all table rows
                    var iColspan = nTrs[0].getElementsByTagName('td').length;
                    var sLastGroup = "";
                    var summed_minutes = 0;

                    for (var i = 0; i < nTrs.length; i++)
                    {
                        var iDisplayIndex = oSettings._iDisplayStart + i;
                        var sGroup = oSettings.aoData[ oSettings.aiDisplay[iDisplayIndex] ]._aData[ 5 ];

                        if ( sGroup != sLastGroup )
                        {
                            var nGroup = document.createElement( 'tr' );
                            var nCell = document.createElement( 'td' );
                            nCell.colSpan = iColspan;
                            nCell.className = "group";

                            summed_minutes += oSettings.aoData[ oSettings.aiDisplay[iDisplayIndex] ]._aData[ 7 ];
                            nCell.innerHTML = sGroup + summed_minutes;
                            nGroup.appendChild( nCell );
                            nTrs[i].parentNode.insertBefore( nGroup, nTrs[i] );
                            sLastGroup = sGroup;
                        }
                    }
                }

            });

        });

The only problem is that the columns I want to sum aren't being added together. The values are being displayed, but like a string instead of adding together like numbers. Also, not all of the values are even being displayed as a string - there are repeats going on. I tried converting using Number() and parseInt() but no luck. I'm trying to put this into the callback function (like the grouping function) so that the values will be summed after each table filter, too.

I'm sure I just have a variable wrong or in the wrong place or something, but I just can't figure it out. Frustratingly close! :-(

TIA, Matt

For adding all cells in 5th columns use this $("table tr td:nth-child(4)").each(function() { total += parseFloat($(this).text()); }); — Lakshmana Kumar
– Lakshmana Kumar, Commented May 25, 2013 at 5:52
Thanks for the help. I'm closer but still not quite there. I've updated my jsfiddle to show where I'm at now. jsfiddle.net/RgKPZ/126 — Matt
– Matt, Commented May 29, 2013 at 4:27
When looking at the fiddle, you'll see the grouped content (job 1, job 2, etc). Directly under that is a zero, which is where I'm trying to put the sum of the "minutes to sum" column. Uncommenting line 48 in the js is where I'm stuck. It doesn't sum correctly (returns NaN) and it replaces the whole row instead of just the div I want to put the sum in. It also seems to only do the first two groups instead of all three. I've tried messing with selectors and other jquery stuff for a while now and can't crack it. — Matt
– Matt, Commented May 29, 2013 at 4:31

Lakshmana Kumar · Accepted Answer · 2013-05-29 08:12:05Z

Try this...

Change all <div id='group_sum'>0</div> to <div class='group_sum'></div> because id should be unique. so use class

See it in fiddle

  $(function() {
    var oTable = $('#job_history').dataTable({
        "aoColumnDefs": [{ "bVisible": false, "aTargets": [4, 5, 6]}],
        "aLengthMenu": [[10, 25, 50, -1], ["Show 10 entries", "Show 25 entries", "Show 50 entries", "Show all entries"]],
        "iDisplayLength": -1,
        "aaSortingFixed": [[5, 'asc']],
        "aaSorting": [[5, 'asc']],
        "bJQueryUI": true,
        "sDom": '<flip>',
        "fnDrawCallback": function(oSettings) {
            if (oSettings.aiDisplay.length == 0) {
                return;
            }

            // GROUP ROWS
            var nTrs = $('#job_history tbody tr');
            var iColspan = nTrs[0].getElementsByTagName('td').length;
            var sLastGroup = "";

            for (var i = 0; i < nTrs.length; i++) {
                var iDisplayIndex = oSettings._iDisplayStart + i;
                var sGroup = oSettings.aoData[oSettings.aiDisplay[iDisplayIndex]]._aData[5];

                if (sGroup != sLastGroup) {
                    var nGroup = document.createElement('tr');
                    var nCell = document.createElement('td');
                    nCell.colSpan = iColspan;
                    nCell.className = "group";
                    nCell.innerHTML = sGroup;
                    nGroup.appendChild(nCell);
                    nTrs[i].parentNode.insertBefore(nGroup, nTrs[i]);
                    sLastGroup = sGroup;
                }
            }
            //-------------------------------------------------
            // SUM COLUMNS WITHIN GROUPS
            var total = 0;
            $("#job_history tbody tr").each(function(index) {
                if ($(this).find('td:first.group').html()) {
                    total = 0;
                } else {
                    total = parseFloat(total) + parseFloat(this.cells[4].innerHTML);
                    $(this).closest('tr').prevAll('tr:has(td.group):first').find("div").html(total);
                }
            });
            //-------------------------------------------------
        }
    });
});

OMG. Thank you so much! I've learned a lot by seeing how you altered my function. Yours seems simpler/more elegant than mine, which is probably why it actually works. :-) And duh on me for the id vs. class thing. Again, thank you! You've made my week!

Collectives™ on Stack Overflow

jquery datatables - sum columns within a group

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related