Sums of several columns at the footer of a table

delph49

Hello,

I tried to have sums of several columns at the footer of my table. I tried differents options but without success.

I tried this : https://datatables.net/examples/advanced_init/footer_callback.html
but I can only have the total of one column.

I tried this: https://datatables.net/examples/plug-ins/api.html
I have all the column like I want with the sum of the sorting but there are buttons. Is there a solution to have this method with sum in the footer of columns?

I tried others thinks too but nothing good for what I want I'm a beginner and Javascript is very difficult for my at the moment.

Thanks for advance.

Delph

kthorngren

Without seeing the code you are trying its hard to say what the problem is. Maybe you can post your Datatables Javascript code.

Kevin

delph49

Ok!
the first was: http://live.datatables.net/yificapi/3/
but it is not exactly that I want because it makes sum to the current page or it takes everything regardless of the selection.

the second: http://live.datatables.net/ceyebeje/1/
Here don't works but at home it's ok: when I click on a button I have the sum of the selection. But I would like this in the footer of the table for each column.

kthorngren

but it is not exactly that I want because it makes sum to the current page or it takes everything regardless of the selection.

Please describe what you mean by "selection". What are you doing to select what you want totaled?

when I click on a button I have the sum of the selection. But I would like this in the footer of the table for each column.

Are you saying that when you click a button you want the total to be shown in the footer?

Kevin

delph49

Sorry, I'm not very precise.

it's the sorting that I do with DataTables.
If for example, in the result of my sort, I have 1 element out of 3 that contains my table, I want the sum of this element and not 3.

And I don't want to click a button to get the result, I want the result directly in my footer.

kthorngren

Sorry, its still not clear to me exactly what you want.

Are you wanting to sum the column that is sorted? If you sort a different column then the total will now be that column?

If so then you may want to use the order() API to get the column(s) that are ordered and use that for the columns that are totaled.

Kevin

delph49

No. For example you have differents columns. One is for years: 2017-2018 for example. But I want just 2018. So the sorting is good on my page, but I want the sums of the others columns ( hours, costs...) just with sorting values and not all the values of the table. And the 1st solution do that (sum of all values and it's not good). The second solution works well for it but it's with button and I don't want button, I wan't the sum on the footer of the table.

kthorngren

Maybe the RowGroup extension would be useful for you. You can get totals per group. Take a look at the examples.

One is for years: 2017-2018 for example. But I want just 2018.

Not sure how you would determine whether to provide the sum for 2018 versus 2017 in the footer. If the RowGroup doesn't fit your needs please help me understand how you want to determine what to sum on based on the sorting of the table.

Kevin

delph49

Hello.
I am sorry for my late response. I don't understand that RowGroup extension do.
I have a table with year, cost, hours.
I have the search box with DataTables.
I put a year in the searchBox (for example 2018).
So the table is sorting with just the data of 2018.
So I want the sum of costs and the sum of hours to 2018.
Those 2 sums could be at the footer of my table.
I don't want compare the 2 years, I want just on a year.

kthorngren

Sorry I didn't realize you were wanting to sum based only on the search results. You can use selector-modifier to process only the rows shown as the result of your search.

Instead of this:

            pageTotal = api
                .column( 4, { page: 'current'} )

Try this:

            pageTotal = api
                .column( 4, { search: 'applied'} )

Kevin

delph49

Thanks! It's seems that it's a response of a part of my problem!

delph49

It's ok for have the sum in all columns, I put the code below. But if someone knows how to make a rounding of sums because some sums are not exact, for example, I have 733,499999999 instead of 733.5. And thanks again @kthorngren

"footerCallback": function (row, data, start, end, display) {
                                    var api = this.api(), data;

                                    // Remove the formatting to get integer data for summation
                                    var intVal = function (i) {
                                        return typeof i === 'string' ?
                                                i.replace(/[\$,]/g, '') * 1 :
                                                typeof i === 'number' ?
                                                i : 0;
                                    };

                                    //Total hours over the search
                                    totalHours = api
                                            .column(4, {search: 'applied'})
                                            .data()
                                            .reduce(function (a, b) {
                                                return intVal(a) + intVal(b);
                                            }, 0);

                                    // Update footer
                                    $(api.column(4).footer()).html(
                                            totalHours
                                            );

                                    //Total labor cost over the search
                                    totalLaborCost = api
                                            .column(6, {search: 'applied'})
                                            .data()
                                            .reduce(function (a, b) {
                                                return intVal(a) + intVal(b);
                                            }, 0);

                                    // Update footer
                                    $(api.column(6).footer()).html(
                                            totalLaborCost
                                            );

                                    //Total material cost over the search
                                    totalMaterialCost = api
                                            .column(7, {search: 'applied'})
                                            .data()
                                            .reduce(function (a, b) {
                                                return intVal(a) + intVal(b);
                                            }, 0);

                                    // Update footer
                                    $(api.column(7).footer()).html(
                                            totalMaterialCost
                                            );

delph49

I found this:

 $(api.column(7).footer()).html(
         Math.round(totalMaterialCost* 100) / 100
                                            );

It's good, I have round of my sum with 2 numbers after the comma