'Laravel Eloquent groupBy() AND also return count of each group

I have a table that contains, amongst other columns, a column of browser versions. And I simply want to know from the record-set, how many of each type of browser there are. So, I need to end up with something like this: Total Records: 10; Internet Explorer 8: 2; Chrome 25: 4; Firefox 20: 4. (All adding up to 10)

Here's my two pence:

$user_info = Usermeta::groupBy('browser')->get();

Of course that just contains the 3 browsers and not the number of each. How can I do this?

laravellaravel-4eloquent


Solution 1:[1]

This works for me (Laravel 5.1):

$user_info = Usermeta::groupBy('browser')->select('browser', DB::raw('count(*) as total'))->get();

Solution 2:[2]

Thanks Antonio,

I've just added the lists command at the end so it will only return one array with key and count:

Laravel 4

$user_info = DB::table('usermetas')
    ->select('browser', DB::raw('count(*) as total'))
    ->groupBy('browser')
    ->lists('total','browser');

Laravel 5.1

$user_info = DB::table('usermetas')
    ->select('browser', DB::raw('count(*) as total'))
    ->groupBy('browser')
    ->lists('total','browser')->all();

Laravel 5.2+

$user_info = DB::table('usermetas')
    ->select('browser', DB::raw('count(*) as total'))
    ->groupBy('browser')
    ->pluck('total','browser');

Solution 3:[3]

If you want to get collection, groupBy and count:

$collection = ModelName::groupBy('group_id')
->selectRaw('count(*) as total, group_id')
->get();

Cheers!

Solution 4:[4]

  1. Open config/database.php
  2. Find strict key inside mysql connection settings
  3. Set the value to false

Solution 5:[5]

Works that way as well, a bit more tidy. getQuery() just returns the underlying builder, which already contains the table reference.

$browser_total_raw = DB::raw('count(*) as total');
$user_info = Usermeta::getQuery()
    ->select('browser', $browser_total_raw)
    ->groupBy('browser')
    ->pluck('total','browser');

Solution 6:[6]

Try with this

->groupBy('state_id','locality')
  ->havingRaw('count > 1 ')
  ->having('items.name','LIKE',"%$keyword%")
  ->orHavingRaw('brand LIKE ?',array("%$keyword%"))

Solution 7:[7]

Laravel Version 8

Removed the dependency of DB

     $counts = Model::whereIn('agent_id', $agents)
        ->orderBy('total', 'asc')
        ->selectRaw('agent_id, count(*) as total')
        ->groupBy('agent_id')
        ->pluck('total','agent_id')->all();

Solution 8:[8]

$post = Post::select(DB::raw('count(*) as user_count, category_id'))
              ->groupBy('category_id')
              ->get();

This is an example which results count of post by category.

Solution 9:[9]

In Laravel 8 you can use countBy() to get the total count of a group.

Check the documentation on the same. https://laravel.com/docs/8.x/collections#method-countBy

Solution 10:[10]

Here is a more Laravel way to handle group by without the need to use raw statements. 

$sources = $sources->where('age','>', 31)->groupBy('age');

$output = null;
foreach($sources as $key => $source) {
    foreach($source as $item) {
        //get each item in the group
    }
    $output[$key] = $source->count();
}

Solution 11:[11]

If you want to get sorted data use this also

$category_id = Post::orderBy('count', 'desc')
    ->select(DB::raw('category_id,count(*) as count'))
    ->groupBy('category_id')
    ->get();

Solution 12:[12]

Simple solution(tested with Laravel 9 and Spatie/Permissions).

Controller:

//Get permissions group by guard name(3 in my case: web, admin and api)
$permissions = Permission::get()->groupBy('guard_name');

View:

@foreach($permissions as $guard => $perm)
  <div class="form-group">
    <label for="permission">Permissions ({{ ucfirst($guard) }}) {{ count($perm) }}</label>
    <select name="permission[]" id="permission" class="form-control @error('permission') is-invalid @enderror" multiple>
      @foreach($perm as $value)
        <option value="{{ $value->id }}">{{ $value->name }}</option>
      @endforeach
    </select>
    @error('permission')
      <div class="invalid-feedback">
        {{ $message }}
      </div>
    @enderror
  </div>
@endforeach

Solution 13:[13]

Another way would be this:


$data = Usermeta::orderBy('browser')->selectRaw('browser, count(*) as total')->get()

Related Questions