How to return total value for each company using e

Question

so i have this table on database

+---------+------------+-------+
| Company |  periods   | value |
+---------+------------+-------+
| a1      | 01-01-2018 |  1000 |
| a1      | 01-02-2018 |   600 |
| a2      | 01-01-2018 |   500 |
| a2      | 01-03-2018 |   500 |
| a3      | 01-01-2018 |   500 |
| a3      | 01-02-2018 |   500 |
| a3      | 01-03-2018 |   500 |
+---------+------------+-------+

and what i expect to get on query is to be like if i want to get all company total value for 01-02-2018 then it will be summarize the value for each company until 01-02-2018

+---------+------------+-------+
| Company |  periods   | value |
+---------+------------+-------+
| a1      | 01-02-2018 |  1600 |
| a2      | 01-02-2018 |   500 |
| a3      | 01-02-2018 |  1000 |
+---------+------------+-------+ 

and if i want to get total value form 01-03-2018 then it also will summarize the value for each company until 01-03-2018

+---------+------------+-------+
| Company |  periods   | value |
+---------+------------+-------+
| a1      | 01-03-2018 |  1600 |
| a2      | 01-03-2018 |  1000 |
| a3      | 01-03-2018 |  1500 |
+---------+------------+-------+ 

how to do that on eloquent?

Answer 1

Use groupBy:

$point_history = Model::select(['Company', 'periods',DB::raw('sum(value) as value')])
                ->groupBy('Company')
                ->having('periods','01-02-2018')
                ->get();

write periods condition in having as your requirement

Answer 2

$from = date('2018-01-01');
$to = date('2018-02-01');

$data = Model::select(DB::raw('Company', 'periods', 'sum(value) as total_value'))
                ->groupBy('Company')
                ->whereBetween('periods', [$from, $to])
                ->get();

Can you try the above code? $form date should be the very first date from where you started recording. You should change the $to date to get the total summery time to time.

Answer 3

the problem is like this,

when you group rows by 'value' column, in the result row what is gonna be the 'periods' value? you know there were several distinct 'period' values before you group. now among them, which 'period' value should MySQL put into your result row...?

so its not allowed by 'sql_mode=only_full_group_by'

either you need to remove 'periods' column from select query or you can aggregate the 'periods' column.

Possible solutions.

$maxDate = // whatever date you wanna compare with.

$summery = Model::select(DB::row('Company, max(periods) AS periods, sum(value) AS value'))
    ->groupBy('Company')
    ->where('periods', '<=', $maxDate)
    ->get();