How to Write Laravel Groupby Query

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

This is working for me:

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

How to write Laravel GroupBy Query?

Try this line:

$doctor_daily=TestingType::join('report_doctor_company_daily_test', 'testing_types.id', '=', 'report_doctor_company_daily_test.test_id')
                          ->join('doctor', 'report_doctor_company_daily_test.doctor_id', '=','doctor.id')
                          ->groupBy('testing_types.id')
                          ->select('report_doctor_company_daily_test.test_id','testing_types.testing_name','doctor.first_name','doctor.last_name',DB::raw('SUM( report_doctor_company_daily_test.test_price )')
                          ->get();

Your mysql version is 5.7+, so check this reference

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list

so check the config/database.php, if your mysql configuration strict is true, you need to disabled the ONLY_FULL_GROUP_BY, add modes like this:

'modes' => [
            //'ONLY_FULL_GROUP_BY',
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_AUTO_CREATE_USER',
            'NO_ENGINE_SUBSTITUTION'
           ],

or you can just use any_value method for the selected columns:

PS: any_value does not exist on MariaDB

->selectRaw('ANY_VALUE(report_doctor_company_daily_test.test_id),
    ANY_VALUE(testing_types.testing_name),
    ANY_VALUE(doctor.first_name),
    ANY_VALUE(doctor.last_name),
    SUM( report_doctor_company_daily_test.test_price')

Laravel group by query

You need to use conditional aggregation here:

return $this->hasMany(Facility::class)
    ->select("facility",
        DB::raw("COUNT(CASE WHEN has_it = 1 THEN 1 END) AS has_it_true"),
        DB::raw("COUNT(CASE WHEN has_it = 0 THEN 1 END) AS has_it_false"))
    ->groupBy('facility');

This would correspond to the following raw MySQL query:

SELECT
    facility,
    COUNT(CASE WHEN has_it = 1 THEN 1 END) AS has_it_true,
    COUNT(CASE WHEN has_it = 0 THEN 1 END) AS has_it_false
FROM port_facilities
GROUP BY
    facility;

Note that I did not include id and port_id in the SELECT clause, because MySQL might not accept those columns. In general, when doing GROUP BY facility, we may only select facility and aggregates of columns other than facility.

How can i orderby and groupby in one query in laravel

$errors = UserErrors::groupBy('feild_name')->orderBy('feild_name','DESC')->get();

Example as below

$query->where('feild_name', '=', 'active')
    ->groupBy('feild_name')
    ->orderBy('date','DESC')
    ->get();

Laravel group by and unique values for each group

You can do simply this way:

$data = Specification::whereIn('product_id', $products->pluck('id'))
    ->groupBy(['specification_key', 'specification_value']) // group by query
    ->get()
    ->groupBy('specification_key'); // group by collection

array:1 [
  "RAM" => array:2 [
    0 => array:6 [
      "id" => 1
      "product_id" => 1
      "specification_key" => "RAM"
      "specification_value" => "16 GB"
      "created_at" => "2021-03-21T15:35:21.000000Z"
      "updated_at" => "2021-03-21T15:35:21.000000Z"
    ]
    1 => array:6 [
      "id" => 3
      "product_id" => 3
      "specification_key" => "RAM"
      "specification_value" => "8 GB"
      "created_at" => "2021-04-16T16:36:03.000000Z"
      "updated_at" => "2021-04-16T16:36:03.000000Z"
    ]
  ]
]

If you have problem with Syntax error or access violation: 1055, you can follow this answer.

Edit your applications's database config file config/database.php
In mysql array, set strict => false to disable MySQL's strict mode

If you want to display specification_value only, you can use mapToGroups() :

$data = Specification::whereIn('product_id', $products->pluck('id'))
    ->groupBy(['specification_key', 'specification_value']) // group by query
    ->get()
    ->mapToGroups(function ($item, $key) {
        return [$item['specification_key'] => $item['specification_value']];
    });

array:1 [
  "RAM" => array:2 [
    0 => "16 GB"
    1 => "8 GB"
  ]
]

How to use Group by query in laravel?

Try using group_concat()

$inkinds = DB::table('inkind')
     ->join('inkind_items', 'inkind.id', '=', 'inkind_items.inkind_id')
     ->select('inkind.*', DB::raw('group_concat(inkind_items.name) as items'))
     ->groupBy('inkind_items.inkind_id')
     ->get();

Here I'm assuming inkind have field name and inkind_items has fields items.

Cancel group by from query in Laravel

You can do it with:

$myQuery->groups = null;