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

55

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

17,072
4
41
53

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

28

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,257
2
42
53

11

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 19 · Answer 3 · answered Jul 27 '19 at 08:15

19

$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

351
3
5

Thanks! In my case I wanted to order by month too. So my `select` became `monthname(created_at) month_name, month(created_at) month` and then `->groupBy('year', 'month', 'month_name')->orderBy('year', 'desc')->orderBy('month', 'desc')`. – JCarlosR Mar 06 '23 at 04:18

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

7

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,991
5
37
46

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

2

$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

39
9

score 2 · Answer 6 · answered Sep 27 '22 at 15:45

The answer by @Amit Gupta is a good, but it isn't working >= MySQL 5.7/MariaDB 10.2 and calls the error:

Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column

To solve that issue, you can use eg. MIN() on the nonaggregated column:

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

ppegu · Answer 7 · 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

362
2
9
22

1

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

Laravel Eloquent, group by month/year

7 Answers7

Linked

Related