Get sum of current and previous row data

Hello,

I am using powergrid table and below is the code to query the database,


    public $symbol;
    public $expiry;
    public $date;
    public $timeFrame;

    public function datasource(): Builder
    {

        $query = LiveFuture::query()
            ->where('date', $this->date)
            ->where('asset_symbol', $this->symbol)
            ->where('expiry', $this->expiry);

        $data = $query->get(); // Retrieve the data

        // Dispatch an event with the data
        $this->dispatch('liveFutureFetched', $data);

        // Return the original Builder instance
        return $query;
    }

Above code will give output of all rows,

Now livefuture contains data with column time where data are imported at 3 min, which means one row with time 10:00, the second row with 10:03, ...10:06,...10:09....and so on.

Now I have a custom filter where I want to fetch data with a defined time frame, say if I select 3 then it will show all rows without any sum of column values, if I select 6 then it needs to do the sum of column values for a row of 10:00 and 10:03 and show output as a combined row in power grid table,

If I select 15, then it should take the sum of 5 rows and show one combined row, I have filter value ```$timeFrame``available to get a filter,

but I am confused about the query, how do I get a sum of column values for defined rows?

enoch91

2 years ago

Level 2

@lacoder based on your scenario, add this block of code right before the $data = $query->get(); line

 // Check if $timeFrame is set and greater than 1
    if ($this->timeFrame && $this->timeFrame > 1) {
        // Calculate the number of rows to include in each group
        $rowsPerGroup = $this->timeFrame / 3; // Assuming each row represents 3 minutes

        // Calculate the group number for each row based on the time
        $query->selectRaw('FLOOR(MINUTE(time) / (:rowsPerGroup * 3)) AS group_number', ['rowsPerGroup' => $rowsPerGroup]);

        // Group the rows based on the calculated group number
        $query->groupBy('group_number');

        // Select the sum of column values for each group
        // Replace column_name with the name of the column for which you want to calculate the sum
        $query->selectRaw('SUM(column_name) AS sum_of_column');
    }

Please or to participate in this conversation.