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

Question

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?

Antonio Carlos Ribeiro · Accepted Answer · 2013-08-30T13:48:05.187

357

This is working for me:

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

edited Aug 30 '13 at 13:48

answered Aug 30 '13 at 13:11

Antonio Carlos Ribeiro

86,191
22
213
204

1

Superb! Just added 'browser' to the select thus: select('browser', ...) and got everything needed. You're good, you! http://www.youtube.com/watch?v=ravi4YtUTxo – kJamesy Aug 30 '13 at 13:35
1

Thanks. But why doesn't it work when used with Models like User::select('country', DB::raw('count(*) as total')->otherMethods() ? – doncadavona Sep 04 '15 at 05:06
1

+v. use \DB instead of DB at controllers – Amit Bera Jan 05 '16 at 15:41
@AmitBera can you explain the reason?, please – JCarlosR Jul 22 '16 at 02:46
@JCarlos Late i know. But the \DB is needed because a controller will be namespaced in current versions of Laravel. The \DB means that your code will look for the DB class at root, rather than in the controllers namespace – Ryk Waters Oct 13 '16 at 10:56
18

Is there any particular reason why you prefer `DB::table('usermetas')->..` over `Usermeta::..` ? – Adam Oct 16 '17 at 08:32
Hi, @Antonio, I'm having an error on calling a member function groupBy() on array. My laravel query is $patients = DB::select('select * from patients') ->groupBy('patients.patient_id') ->get(); How can I make this work? Thanks – Two Nov 04 '19 at 15:26
I know it's an old post, however, I was wondering if someone just figure out how to get 'total records' results as mentioned in the question. – Brayam Valero Apr 15 '21 at 17:51
how we can sort this query by count? – Mohad Hadi Jan 04 '23 at 15:29

score 54 · Answer 2 · edited Jul 22 '16 at 02:59

54

This works for me (Laravel 5.1):

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

edited Jul 22 '16 at 02:59

Amit Bera

7,581
7
31
57

answered Jul 20 '16 at 10:00

carlituxman

1,201
12
22

score 43 · Answer 3 · edited Dec 11 '21 at 09:17

43

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');

edited Dec 11 '21 at 09:17

ManojKiran A

5,896
4
30
43

answered Aug 11 '15 at 15:34

Diogo Gomes

2,135
16
13

2

Thanks. One note: ->all() in the 5.1 example should be removed, since you already lists the results. – Pim Feb 27 '17 at 14:04
1

`list()` is deprecated and renamed to `pluck()` https://laravel.com/docs/5.2/upgrade#upgrade-5.2.0 – Arun Code Nov 25 '17 at 05:14
how to sort with total after pluck and take 10 high count from this – Hossein Azad Apr 17 '21 at 10:51
i founded tnx usermetas::groupBy('browser') ->selectRaw('count(*) as total, browser') ->orderBy('total','DESC') ->skip(0)->take($number)->pluck('browser'); – Hossein Azad Apr 17 '21 at 11:16

score 37 · Answer 4 · answered Aug 17 '18 at 08:42

37

If you want to get collection, groupBy and count:

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

Cheers!

answered Aug 17 '18 at 08:42

Adam Kozlowski

5,606
2
32
51

score 14 · Answer 5 · edited Jan 09 '19 at 11:17

14

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

edited Jan 09 '19 at 11:17

Jignesh Joisar

13,720
5
57
57

answered Aug 27 '18 at 10:58

Boris Tetřev

278
1
4
9

the perfect way to avoid the raw query !! – Ranjan Fadia Jan 07 '21 at 11:15
5

The perfect way to mess your future sql codes up! And not be able to run it on a standard database without modification. – Steve Moretz Sep 14 '21 at 07:13

score 10 · Answer 6 · edited Oct 22 '21 at 12:17

10

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');

edited Oct 22 '21 at 12:17

Maik Lowrey

15,957
6
40
79

answered Nov 12 '15 at 16:08

Yauheni Prakopchyk

10,202
4
33
37

score 4 · Answer 7 · answered Dec 07 '17 at 11:11

4

Try with this

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

answered Dec 07 '17 at 11:11

Jasim Juwel

736
8
19

3

While this may answer the question, it is better to explain the essential parts of the answer and possibly what was the problem with OPs code. – pirho Dec 07 '17 at 11:35
Thanks! The `havingRaw` bit is really nice since it lets you also lets you filter out the results you don't want based on the count. Comes in handy when you want to do some "and-filtering" and have joins for each filter. – Arno van Oordt Nov 19 '20 at 17:23

score 4 · Answer 8 · edited Sep 28 '20 at 22:07

4

$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.

edited Sep 28 '20 at 22:07

Tony

9,672
3
47
75

answered Jun 15 '20 at 14:02

Yuvraj Hinger

198
1
6

score 4 · Answer 9 · answered May 26 '21 at 02:38

4

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();

answered May 26 '21 at 02:38

Alaksandar Jesus Gene

6,523
12
52
83

WilliamStam · Answer 10 · 2022-11-03T09:13:01.587

Since this is the top result when i search for eloquent count with groupby returns only first

using "illuminate/database": "^9.38" in composer. so "should" be the latest at the time of this post

I honestly have no idea why they think that returning the first record is the right option.. ie

IMHO the current implementation doesn't make sense for queries including groupBy statements. Why should be N_1 be the "right" result?

Since @taylorotwell rightly pointed out some performance issues with counting the subquery results, why don't we fix that on the php side, by checking if there are any group statements, and if so, performing a N_1 + N_2 + .... + N_M ?

https://github.com/laravel/ideas/issues/1693#issuecomment-621167890

Wrapping the query and doing a count seems to work for me

$records = ...

$record_count = DB::table( "fake" );
$record_count->fromSub($records->select(DB::raw(1)),"query");
$record_count->count();

create a "fake" query builder
add a sub "from" from (...) query set the "sub query" to select 1 instead of returning huge column data. not sure if this is needed but in my mind it seems like a good idea
do the normal ->count()

returns the "expected" result since it executes:

select count(*) as aggregate from (select 1 from ... group by ...) as `query`

The sub is the "query" that $records would normaly execute

My use-case is for pagination (not using laravel). so i get the record count then pass it to the paginator then call ->forPage()->get()

https://github.com/laravel/framework/issues/44081#issuecomment-1301816710

I've been searching for hours only to find that its a known issue. Thank you so much mate — Daniyal Nasir, Dec 18 '22 at 16:12
For someone supporting a version lower than 5.8 you need do it like [this](https://stackoverflow.com/a/24838367/4308270) (working on my 5.5). since `fromSub` isn't available for versions before 5.8 . — Daniyal Nasir, Dec 18 '22 at 16:32

score 2 · Answer 11 · answered Jun 02 '22 at 19:16

2

Another way would be this:


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

answered Jun 02 '22 at 19:16

Steven

51
1
7

score 1 · Answer 12 · answered Feb 04 '23 at 16:16

Laravel Eloquent query that uses the GROUP BY clause with advanced aggregation functions and conditional statements.

GroupBy on one column.

$data = Employee::select(
  'department', 
  DB::raw('SUM(salary) as total_salary'), 
  DB::raw('COUNT(*) as total_employees'), 
  DB::raw('SUM(IF(bonus > 13000, 1, 0)) as employees_with_bonus')
)
->groupBy('department')
->havingRaw('total_employees > 5 AND total_salary > 10000')
->orHavingRaw('department_rank LIKE ?', array("%$keyword%"))
->get();

In the above query, if the bonus is greater than 13000, the IF function returns 1 otherwise it returns 0.

GroupBy on two columns: The groupBy method takes multiple arguments

$data = Employee::select(
  'department', 
  'location', 
  DB::raw('SUM(salary) as total_salary'), 
  DB::raw('COUNT(*) as total_employees'), 
  DB::raw('SUM(IF(bonus > 1000, 1, 0)) as employees_with_bonus')
)
->groupBy('department', 'location')
->havingRaw('total_employees > 5 AND total_salary > 10000')
->get();

GroupBy and JOIN: Laravel Eloquent query that joins two tables and uses grouping and aggregate functions. (inner join)

$data = Employee::select(
  'employees.department', 
  'employees.location', 
  DB::raw('SUM(employees.salary) as total_salary'), 
  DB::raw('COUNT(*) as total_employees'), 
  DB::raw('SUM(IF(employees.bonus > 1000, 1, 0)) as employees_with_bonus')
)
->join('departments', 'employees.department', '=', 'departments.name')
->groupBy('employees.department', 'employees.location')
->havingRaw('total_employees > 5 AND total_salary > 10000')
->get();

Raw MySQL Query:

SELECT 
  department, 
  SUM(salary) AS total_salary, 
  COUNT(*) AS total_employees, 
  SUM(IF(bonus > 1000, 1, 0)) AS employees_with_bonus 
FROM 
  employees 
GROUP BY 
  department 
HAVING 
  total_employees > 5 AND total_salary > 13000;

score 0 · Answer 13 · edited Oct 22 '21 at 12:19

0

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();

edited Oct 22 '21 at 12:19

Maik Lowrey

15,957
6
40
79

answered Jun 15 '20 at 14:08

Yuvraj Hinger

198
1
6

score 0 · Answer 14 · answered Feb 04 '22 at 12:50

0

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

answered Feb 04 '22 at 12:50

Abe Nalisi

57
3

5

This is for Collections and not for Eloquent models. It would be inefficient to get all of the records from the database and then perform a groupby. – Flipper Feb 28 '22 at 20:31

score 0 · Answer 15 · answered May 13 '22 at 13:08

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

score 0 · Answer 16 · answered Mar 10 '23 at 15:31

It prints all columns data. It works for me:

$user_info = DB::table('usermetas as u1')
    ->leftJoin('usermetas as u2', function ($join) {
        $join->on('u1.browser', '=', 'u2.browser')
             ->whereRaw('u1.created_at < u2.created_at');
    })
    ->where('u2.id', '=', null)
    ->select('u1.*')
    ->orderBy('u1.id')
    ->get();

score 0 · Answer 17 · edited Mar 17 '23 at 14:11

0

#laravel9 :

User::select('city','count, count(*) as total')->groupBy('city')->get();

edited Mar 17 '23 at 14:11

Moritz Ringler

9,772
9
21
34

answered Mar 17 '23 at 10:07

Sahil Markana

1
4

score -2 · Answer 18 · edited Apr 16 '18 at 09:03

-2

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();
}

edited Apr 16 '18 at 09:03

masud_moni

1,121
16
33

answered Mar 28 '18 at 22:24

Vulfoliac

13
2

12

This is memory and processing hungry. – doncadavona Jun 04 '18 at 03:46
Same memory pb for me – Vince Jun 15 '20 at 14:09

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

18 Answers18

Try with this

Linked

Related