'How to get all month record count in laravel

I have applied group by with with created_at column using DB query but like to do it by laravel eloquent

Output should be like this:-

Array
(
  [1] => 2
  [2] => 3
  [3] => 7
  [4] => 4
  [5] => 5
  [6] => 7
  [7] => 2
  [8] => 9
  [9] => 0
  [10] => 4
  [11] => 0
  [12] => 0
)

Please provide any help me to do this.

phpmysqllaravel-5


Solution 1:[1]

If your created_at column is DATETIME, you can group it like this

Entity::orderBy(...)->groupBy(DB::raw('MONTH(created_at)'))

You can also select the month in advance

Entity::select(DB::raw('MONTH(created_at) as month')->groupBy('month')->get()->keyBy('month');

Solution 2:[2]

Modifing Ismael Ansari Post,

 $users = User::select('id', 'created_at')
        ->get()
        ->groupBy(function ($date) {
            return Carbon::parse($date->created_at)->format('m');
        });

    $usermcount = [];
    $userArr = [];

    foreach ($users as $key => $value) {
        $usermcount[(int)$key] = count($value);
    }

    $month = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];

    for ($i = 1; $i <= 12; $i++) {
        if (!empty($usermcount[$i])) {
            $userArr[$i]['count'] = $usermcount[$i];
        } else {
            $userArr[$i]['count'] = 0;
        }
        $userArr[$i]['month'] = $month[$i - 1];
    }

    return response()->json(array_values($userArr));

Output:

[
    {
        "count": 1,
        "month": "Jan"
    },
    {
        "count": 0,
        "month": "Feb"
    },
    {
        "count": 0,
        "month": "Mar"
    },
    {
        "count": 0,
        "month": "Apr"
    },
    {
        "count": 0,
        "month": "May"
    },
    {
        "count": 0,
        "month": "Jun"
    },
    {
        "count": 0,
        "month": "Jul"
    },
    {
        "count": 0,
        "month": "Aug"
    },
    {
        "count": 0,
        "month": "Sep"
    },
    {
        "count": 0,
        "month": "Oct"
    },
    {
        "count": 0,
        "month": "Nov"
    },
    {
        "count": 0,
        "month": "Dec"
    }
]

Solution 3:[3]

If we want to get the count based on each month in different years we use the following code

return $usersPerMonth =User::select(DB::raw('count(id) as `data`'),DB::raw("DATE_FORMAT(created_at, '%Y-%m') new_date"))
        ->groupBy('new_date')->orderBy('new_date')->get();

Solution 4:[4]

you can select month ,day, year wise data

$data = Job::select(DB::raw("year(created_at) as year,DAY(created_at) as 
          day,MONTHNAME(created_at) as monthname"))
        ->whereYear('created_at', date('Y'))
        ->get();

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3 Vajiheh Habibi
Solution 4 Aadrsh Sharma

Related Questions