The "groupBy()" method is available to the "DB" facade and you can use it as follows. Do note that in order to "group by" similar or related records you need to have something for it to count to. In the code example below it's the "user_count".
<?php
$users = DB::table('users')
->select(DB::raw('count(*) as user_count, status'))
->where('status', '<>', 1)
->groupBy('status')
->get();In addition to that, you can also mix "groupBy" with "having" and it's like below. This is very useful when you are grouping a related column but ensure that the condition for that query is met hence it's using the "having" function.
<?php
$users = DB::table('users')
->groupBy('account_id')
->having('account_id', '>', 100)
->get();The method signature is the same as the regular "where()" method.
<?php
$users = DB::table('users')
->groupBy('first_name', 'status')
->having('account_id', '>', 100)
->get();Grouping by record with "havingRaw()" method.
<?php
$orders = DB::table('orders')
->select('department', DB::raw('SUM(price) as total_sales'))
->groupBy('department')
->havingRaw('SUM(price) > ?', [2500])
->get();
Leave a reply