Conditional sum

andreavellone

I had to sum the values only if other values are the same.
I have a table like this

item_id       quantity

1                    10
2                      7
1                    12
3                      5
2                    11
1                      3

I had to sum the quantity with the same item_id
so: item_id (1) =sum(10, 12, 3)

First of all, it's possible using render?
Or i must use some Php function?

Thank a lot for any ideas

rf1234

You can use this: https://datatables.net/reference/api/rows().every()

You can do anything in the function that it provides. See the last example on that page.

In addition here is an example from my own coding in which I set all values for a certain column.

var numberRenderer = $.fn.dataTable.render.number( '.', ',', 2 ).display;
yourTable.rows().every( function (rowIdx, tableLoop, rowLoop) {
        var data = this.data();
        var rowTotal;  
        if ( ! isNaN( parseFloat(rowTotal) ) ) {             
            accum += parseFloat(rowTotal);
            data.cashflowAccum = numberRenderer(accum);
        } else {
           data.cashflowAccum = '';
        }
        if (data.cashflow.position == 'L') {
            data.cashflowAccum = '';
        }
        this.invalidate();
    });

rf1234

Forgot mention: I do all of this triggered by the "draw" event. Subsequently the column is being rendered like any other column.

allan

Its possible to be more concise with the DataTables API as well: you could use rows().data() to get the data for the row, then filter() to filter it down to just item_id 1 and use pluck() to get the value for the quantity and finally the sum() plug-in to get the summation.

table
  .rows()
  .data()
  .filter( function ( d ) {
    return d.item_id == 1;
  } )
  .pluck( 'quantity' )
  .sum();

Allan

andreavellone

I tried something like this:
{ data: "ol", editField: "movimenti.ad_ol_id", render: function ( data, type, row ) { return data.id +' '+ data.fase +' '+data.centrale+'('+data.filter( function ( d ) { return d.materiale_id == 66;} ).pluck( 'var' ).sum();+')';}},

but the answer is "data.filter is not a function"...
any ideas?

kthorngren

In this case data is not a DT API which is why you are getting the error. I think the best place for this is in the drawCallback function so it an calculate your totals each time Datatables draws the table.

Kevin