# Row grouping for repeated values

Hi all,

How can i achieve something like this?

How should i build it so that some columns to calculate aggregate values (sums)?

Any help would be appreciated.

Hi @mihalisp ,

Cheers,

Colin

Ok.

In row Grouping i want to sum a date column (HH:mm) and show it beside of the group name.
i have already summed them with success with the following in drawCallback:

`````` var api = this.api() , data;
total_secs = api.cells( null, 17 ,{ page: 'current'} ).render('display').sum();
total          = secondsToTime(total_secs);
``````

I don't understand how to use it instead of

``````   var ageAvg = rows
.data()
.pluck(17)
.reduce( function (a, b) {
return a + b*1
;
}, 0);
``````

Can you help me?

Can anyone suggest something here?

Why `return a+b`?

edited May 12

Why return a+b?

You will want to read up on the Javascript reduce() method.

I'm not really sure what you want to use. I would start by using `console.log(a, b);` in the `reduce()` method to see what the values are. That may give you an idea of what is needed for the total. Just a guess but you may need something like `return a + secondsToTime(b);`

Kevin

I am trying to use Row Group in Datatables to sum time (HH:mm) per user.

But i still get 00:00 for all grouped users.
What am i doing wrong?

`````` rowGroup: {

endRender: function ( rows, group ) {

const sum_rendered1 = rows
.data()
.pluck('eisodos')

console.log([Math.floor(sum_rendered1.asHours()), sum_rendered1.minutes()].join(':'));

//or 2nd way

var sum_rendered2  =  rows
.data()
.pluck(17)
.reduce( function ( a, b ) {

return moment.duration(a).asMilliseconds() +  moment.duration(b).asMilliseconds()
}, 0);

sum_rendered2 = moment.utc(sum_rendered2).format("HH:mm");

console.log(sum_rendered2);
return group +' ('+sum_rendered1+')'+' ('+sum_rendered2+')'
;
},
startRender: null,
dataSrc: 'last_name'
}
``````

In order to help we will need to see your data. Please put together a simple test case with an example of your data.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

The column with the HH:mm format dates is a computed column.
Is this relevant?

edited May 13

The column with the HH:mm format dates is a computed column.
Is this relevant?

Not sure without trying it. Can you provide a simple test case so we can see exactly what you have? This will make helping you easier for us.

What output did you see when using `console.log(a, b);` in the reduce method?

Kevin

edited May 13

Where exactly should i put `console.log(a, b);` ?

The 2 'console' i have in the above code return

1) 0:0

2) 00:00

How can i debug it?How to put a break in each step to see what it returns each time?

Most important,how to see which values from column (in the array returned) are affected??

Thanks.

I would suggest starting off with a simpler example to understand how the reduce method works. Take a look at this one with some strategically placed console.log statements:
http://live.datatables.net/yumiviko/1/edit

It is grouping and summing the age column.

In the reduce() method docs it explains what the `a` and `b` parameters are. The first parameter is the accumulator and the second is the current value. So you don't want to apply datetime or other methods to the `a` accumulator value. It is the subtotal for the loop.

Maybe you can update my example with your data and `columns.render` function.

Kevin

I try with another column which has only a fixed number,but i get the same errors.

``````  "render":  function (data, type, row) {
//return moment.duration(row[14]).asMilliseconds()
return '2 ' ; }  // or 2
``````

I try a simple sum in endRender:

``````      endRender: function ( rows, group //, row, data, start, end, display
) {
console.log('\nGroup:', group);
var total = rows
.data()
.pluck(20)
.reduce( function (a, b) {
console.log('a', a);
console.log('b', b);
return a + b;
}, 0);

console.log('Group Total:', total);
return \$('<tr/>')
.append( '<td colspan="3">Sum for '+group+'</td>' )
.append( '<td>'+total+'</td>' )
;
``````

This is the result:
a 0
b undefined
a NaN
b undefined
Group Total: NaN

Why it always return `undefined`?

edited May 14

return '2 ' ; } // or 2

This is returning a string. Removing the quotes should return an integer.

In the exemple you are starting from it has `return a + b*1;`. I believe the `*1` is a technique for Javascript to convert the string based number to a real number for the equation.

Why it always return undefined?

Without actually seeing it its hard to say. Please build a simple test case or update mine so we can more effectively help you.

Kevin

edited May 14

The column i want to sum is a computed one.

For this column data:null,so how should i use .pluck?

.pluck(what?)

.pluck(20) doesn't work,even if i set this column to return 2 or "2".

the 20th column is a computed one not a column in the JSON output from db.

I also believe that *1 is necessary after `return a + b`.

For some reason in my case b is always undefined.

What's the difference with api.column() instead of rows.data()?

Thanks

Sorry,i just found this :

where Allan suggests that for a computed column `rows.cells( rows.indexes(), 7 ).render()` should be used.

What exactly should this replace?

``````    rows
.data()
.pluck( 20 )
``````

Here is an updated example of what Allan suggested:
http://live.datatables.net/cixekuqi/1/edit

Is there a reason you won't build a test case for us to help you? Shouldn't be that difficult. Just put a sample of your data in the table.

Kevin

It worked!!!

I am really sorry,next time i will build a test case.
It is not difficult after all.

I think that Group Row Sum for cells in a rendered column is a common issue for many people.

I also use the Rows Group plugin from here https://datatables.net/forums/discussion/29319

When i use them both at the same time,the Sum row for Group is shown two times.
Have you got any idea why this is happening?

Thank you so much Kevin!

We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

ok i will make a test case,but how i will add the js of the custom RowsGroup plugin in the test case?

You can include arbitrary libraries on http://live.datatables.net , JSFiddle, CodePen, etc.

Allan

http://live.datatables.net/cixekuqi/3/edit?html,js,output

Thank you guys,your help is appreciated!

Hi @mihalisp ,

That demo helped, thanks. The issue is because you've got that third-party RowGroup included as well - both are displaying that footer information. You'll need to decide which one to use, because as you're seeing, they're going to stumble over each other.

Cheers,

Colin

Both calculate correctly the sum.

Which line of code should i omit from one or the other,so that the sum row doesn't show twice?

edited May 17

It's your choice - I imagine both extensions have pros/cons, so whichever works for you.

This is the third-party extension:

``````rowsGroup:[1,0],
``````

and the block starting with this is ours:

``````rowGroup: {
``````

Cheers,

Colin