Search code examples
gridviewyii2rollupsubtotalcsqldataprovider

Yii2 gridview running total for each SqlDataProvider row

I have a gridview with an attribute amount. I would like to do a running total ( or subtotal / rollup ) of amount for each row. E.g.:

   amount  rollup
1.   2       2
2.   3       5
3.   2       7
4.   1       8

Is there a way to do this? Can you please point me to the right direction? I have no idea how to do this, and I don't find any relevant info, sorry.

Ziki: my $dataProvider is an SqlDataProvider so I can't use this solution. Can you maybe help me a little bit out how I can adjust it to an SqlDataProvider?

I'm referring to values like $data["amount"]. It seems I don't have any key or index, can it be? I'm getting:

Undefined offset: 0 (or 1 or...)

It would be okay also if there was a SQL solution

Solution

  • I have found a simple SQL solution for that:

    sum(amount) over (order by ... rows unbounded preceding)

    The most important detail here is this: rows unbounded preceding

    If you want the sum like this (so slide one row below):

       amount  rollup
1.   2       
2.   3       2
3.   2       5
4.   1       7

    can be done with window function:

    sum(amount) over (order by ... ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)

    Credits to @Hart CO