How to group and aggregate data using Eloquent's "groupBy" and "aggregate" methods in Laravel?

To group and aggregate data using Eloquent's "groupBy" and "aggregate" methods in Laravel, follow these steps:

Define the model for the table you want to query data from. For example, if you have a "users" table, create a "User" model.
In your controller or wherever you want to perform the query, import the model at the top using the use statement. For example, use App\Models\User;.
Use the groupBy method on the model to group the data based on a specific column. You can chain multiple groupBy methods to group by multiple columns. For example:

User::groupBy('status')->groupBy('country')->get();

This will group the data by the "status" and "country" columns.

Use the aggregate method on the model to perform aggregations on the grouped data. The aggregate method takes two parameters: the aggregate function (e.g., "count", "sum", "avg", etc.), and the column to perform the aggregation on. For example:

User::groupBy('status')->selectRaw('count(*) as count')->get();

This will count the number of records in each group.

You can also perform multiple aggregations at once by chaining multiple selectRaw methods. For example:

User::groupBy('status')
    ->selectRaw('count(*) as count')
    ->selectRaw('sum(age) as age_sum')
    ->get();

This will count the number of records and calculate the sum of ages in each group.

Note: You can also use other aggregate functions such as "max", "min", "avg", etc. instead of "count" and "sum" as per your requirements.

Finally, you can access the grouped and aggregated data using the standard Eloquent query methods like get(), first(), pluck(), etc. For example:

$users = User::groupBy('status')->selectRaw('count(*) as count')->get();

foreach ($users as $user) {
    echo $user->status . ' => ' . $user->count;
}

This will loop through the grouped and aggregated data and echo the status and the count for each group.

That's it! By using the groupBy and aggregate methods in Laravel's Eloquent, you can easily group and aggregate data based on your requirements.