Laravel Eloquent – Fetch summaries substring groupedBy and sum total field of group

  database, eloquent, laravel, mysql

I am trying to fetch a grouped By list of all my Summaries with the according Sum of the total field. To visualise it it would look something like this

ID. Code.       Total.      Created_at.         Updated_at
1   61B5A4C5    1437.64     2021-06-22 11:18:09 2021-06-22 11:18:09
2   A9B5311C    711.44      2021-06-22 11:19:10 2021-06-22 11:19:10
3   1426CDC5    711.44      2021-06-22 11:19:27 2021-06-22 11:19:27
4   8684A47A    1007.32     2021-06-22 11:31:54 2021-06-22 11:31:54
5   68B2C2AE    1238.10     2021-06-22 11:32:22 2021-06-22 11:32:22
6   D0D08082    864.33      2021-06-22 11:51:54 2021-06-22 11:51:54
7   1A448FC5    864.33      2021-06-22 11:53:38 2021-06-22 11:53:38
8   25DA8382    375.92      2021-06-22 11:54:41 2021-06-22 11:54:41
9   B81E41CA    711.44      2021-06-23 12:06:39 2021-06-23 12:06:39
10  A4302991    10233.64    2021-06-23 12:08:43 2021-06-23 12:08:43

And then the output would be something similar to this.

Sum.    Created_at
5000.00 2021-06-22 
3999.00 2021-06-23 

But how can I accomplish this using Eloquent? The created_at field is almost always random so I have to substring the value so it will always pick the first 10 letters, which would be 2021-06-22. And the sum should be the sum of all the total fields within the group

I tried

Summary::groupBy(DB::raw('substr(created_at, 0, 9)'))
->selectRaw('*, sum(total) as sum')
->get());

Or

Summary::groupBy('created_at')->sum('total')

But that does not work. How can I achieve this?

Source: Laravel

Leave a Reply