Laravel Eloquent, group by month/year

Question

i'm trying to group my data by month and year.

$data ->select(DB::raw('count(id) as `data`'),DB::raw('YEAR(created_at) year, MONTH(created_at) month'))
           ->groupby('year','month')
           ->get();

The output is :

{
"data": 19215,
"year": 2016,
"month": 10
},

if i group only by month, i don't know from which year belong this month, my expected output is :

{
"clicks": 19215,
"month": 11-2016,
},
{
"clicks": 11215,
"month": 12-2016,
},

i want to do it in sql, not in php.

Amit Gupta · Accepted Answer · 2016-11-10T15:05:11.447

46

You can try as:

->select(DB::raw('count(id) as `data`'), DB::raw("DATE_FORMAT(created_at, '%m-%Y') new_date"),  DB::raw('YEAR(created_at) year, MONTH(created_at) month'))
->groupby('year','month')
->get();

edited Nov 10 '16 at 15:05

answered Nov 10 '16 at 14:02

Amit Gupta

16,069
2
37
51

4

QLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column – TheShun Nov 11 '16 at 01:48
1

This is related to MySQL. Please check this http://stackoverflow.com/q/34115174/6040777 – Amit Gupta Nov 11 '16 at 02:58
Not Mysql 5.7 compliant – Harry Bosh Nov 27 '16 at 08:18
The groupby will fail – Harry Bosh Nov 28 '16 at 05:00
I have already commented above it's related to MySQL check this http://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql. And OP is already using `groupBY` in his query and he doesn't have any problem with that. It's totally off topic here to ask about MySQL compliant. – Amit Gupta Nov 28 '16 at 05:10
Wrong answer @AmitGupta – Shahid Karimi Mar 09 '19 at 15:45
my query problem has been solved with that solution. Thanks – user2727841 Jan 27 '20 at 12:12

Brane · Answer 2 · 2020-06-22T11:07:01.237

21

Note: This is solution based on eloquent, and yes, it not the best approach, but as the question is, this is using eloquent. If you want faster way, you can do it with raw query.

If you want to group your results for example by year, you can use closure where you will parse your datetime to desired format (year or month).

Here is example of the code:

$res= ModelName::where('someColumn','test')
      ->get()
      ->groupBy(function($val) {
      return Carbon::parse($val->created_at)->format('Y');
});

edited Jun 22 '20 at 11:07

answered Jul 28 '18 at 14:44

Brane

3,041
2
35
52

7

This actually fetches every row from database into laravel and then does grouping in php. not recommendable approach. – Simon Fakir May 06 '19 at 16:40
This is solution based on eloquent, and yes, it not the best approach, but as the question is, this is using eloquent. If you want faster way, you can do it with raw query. – Brane May 06 '19 at 17:26
how to group by year and her months – Abdo-Host Sep 28 '19 at 21:42
@Brane how to group by year and her months, would we return this `Carbon::parse($val->created_at)->format('M-Y');` ?? – Dean Van Greunen Apr 20 '21 at 18:05
1

@DeanVanGreunen from the documentation, the syntax is probably like this `->format('F Y');` – Brane Apr 20 '21 at 18:35
@Brane so then it would be `->format('Y-F');` for `2021-04` ? – Dean Van Greunen Apr 20 '21 at 18:49
Probably yes, I haven't test it. – Brane Apr 21 '21 at 06:49

score 10 · Answer 3 · answered Jul 27 '19 at 08:15

10

$result = ModelName::selectRaw('year(created_at) year, monthname(created_at) month, count(*) data')
                ->groupBy('year', 'month')
                ->orderBy('year', 'desc')
                ->get();

answered Jul 27 '19 at 08:15

otim fredrick

141
1
5

ctf0 · Answer 4 · 2020-08-08T10:21:42.290

3

for latest version of mysql, the accepted answer will give error, so instead try

$data
    ->selectRaw("
        count(id) AS data, 
        DATE_FORMAT(created_at, '%Y-%m') AS new_date, 
        YEAR(created_at) AS year, 
        MONTH(created_at) AS month
    ")
    ->groupBy('new_date')
    ->get();

ref: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

edited Aug 08 '20 at 10:21

answered Aug 08 '20 at 10:02

ctf0

6,444
5
34
45

score 1 · Answer 5 · answered Sep 05 '19 at 07:30

1

$data ->select(DB::raw('count(id) as `data`'),DB::raw('YEAR(created_at) year'),DB::raw('MONTH(created_at) month'))
       ->groupBy(DB::raw('YEAR(created_at)'), DB::raw('MONTH(created_at)'))
       ->get();

answered Sep 05 '19 at 07:30

umar Aslam

31
8

ppegu · Answer 6 · 2020-06-15T13:04:39.567

1

=>this might help someone in 2019

$date = "2019-06-16";

return date('m', strtotime($date)); //this return you 06(edited)

so you can do foreach by this method , so you don't need to do the all sql things..

edited Jun 15 '20 at 13:04

answered Sep 16 '19 at 06:16

ppegu

329
2
8
20

1

did you mean 06? – Bariq Dharmawan Jun 10 '20 at 05:10

Laravel Eloquent, group by month/year

6 Answers6

Linked

Related