Question 244813 in Laravel Question posted in
The official Laravel Documentation can be found here.

Issues with Laravel groupBy

I am using Laravel 10.

This is crm_details table:

crm_details table

I need to count each status,. Also I need to consider parent_token with the same value as a single row. That means 2 rows have same `parent_token’ then It will consider only latest Output will be Yes: 2, No :2

$salesCounts = DB::table('crm_details')
    ->select( DB::raw('count(crm_details.status) as count'),'crm_details.parent_token')
    ->groupBy('crm_details.status', 'crm_details.parent_token')
    ->get();

dd($salesCounts);

But I am getting all the data, what is wrong?

Tags:

2

Answers


  1. 0

    You are almost there, try with:

    $salesCounts = DB::table('crm_details as c1')
    ->select('c1.status', DB::raw('count(*) as count'))
    ->leftJoin('crm_details as c2', function($join) {
        $join->on('c1.parent_token', '=', 'c2.parent_token')
             ->where('c1.id', '<', 'c2.id');
    })
    ->whereNull('c2.id')
    ->groupBy('c1.status')
    ->get();

dd($salesCounts);
    Login or Signup to reply.
  2. 0

    In MySQL, the GROUP BY operation is used to group rows based on similar values in the specified columns. When you use GROUP BY on two columns, it combines rows where both of those columns have identical values across all rows.

    There are no other rows in your dataset where the combination of parent_token as ‘FFF’ and status as ‘Yes’ repeats. For instance, in the first row, parent_token is ‘FFF’ and status is ‘Yes,’ and there are no other rows in the table with this exact combination of values.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search