Laravel GroupBy: Practical Examples

 

Using the `GroupBy` functionality in Laravel's Eloquent ORM allows you to efficiently aggregate and manipulate data within your database. Here are some practical examples of using `GroupBy` in Laravel:

Example 1: Grouping by a Single Column

Suppose you have a `sales` table and you want to get the total sales for each product.

use App\Models\Sale;
use Illuminate\Support\Facades\DB;

$salesByProduct = Sale::select('product_id', DB::raw('SUM(amount) as total_sales'))
    ->groupBy('product_id')
    ->get();

foreach ($salesByProduct as $sale) {
    echo "Product ID: " . $sale->product_id . " - Total Sales: " . $sale->total_sales . "<br>";
}

Example 2: Grouping by Multiple Columns

If you want to group sales by both `product_id` and `region` to get the total sales for each product in each region:

$salesByProductAndRegion = Sale::select('product_id', 'region', DB::raw('SUM(amount) as total_sales'))
    ->groupBy('product_id', 'region')
    ->get();

foreach ($salesByProductAndRegion as $sale) {
    echo "Product ID: " . $sale->product_id . " - Region: " . $sale->region . " - Total Sales: " . $sale->total_sales . "<br>";
}

Example 3: Counting the Number of Records in Each Group

Suppose you have a `users` table and you want to count the number of users in each `country`:

use App\Models\User;

$usersByCountry = User::select('country', DB::raw('COUNT(*) as user_count'))
    ->groupBy('country')
    ->get();

foreach ($usersByCountry as $user) {
    echo "Country: " . $user->country . " - User Count: " . $user->user_count . "<br>";
}

Example 4: Grouping and Filtering

If you want to get the total sales for each product but only for sales made in the year 2023:

$sales2023ByProduct = Sale::select('product_id', DB::raw('SUM(amount) as total_sales'))
    ->whereYear('created_at', 2023)
    ->groupBy('product_id')
    ->get();

foreach ($sales2023ByProduct as $sale) {
    echo "Product ID: " . $sale->product_id . " - Total Sales in 2023: " . $sale->total_sales . "<br>";
}

Example 5: Using Having Clause with GroupBy

If you want to get products that have total sales greater than 1000:

$salesByProduct = Sale::select('product_id', DB::raw('SUM(amount) as total_sales'))
    ->groupBy('product_id')
    ->having('total_sales', '>', 1000)
    ->get();

foreach ($salesByProduct as $sale) {
    echo "Product ID: " . $sale->product_id . " - Total Sales: " . $sale->total_sales . "<br>";
}

Example 6: Combining GroupBy with Join

If you want to get the total sales for each category, assuming you have a `categories` table and `sales` table where each sale has a `category_id`:

use App\Models\Category;
use App\Models\Sale;

$salesByCategory = Category::select('categories.name', DB::raw('SUM(sales.amount) as total_sales'))
    ->join('sales', 'categories.id', '=', 'sales.category_id')
    ->groupBy('categories.name')
    ->get();

foreach ($salesByCategory as $category) {
    echo "Category: " . $category->name . " - Total Sales: " . $category->total_sales . "<br>";
}

Example 7: Grouping by Date

If you want to group sales by day and get the total sales for each day:

$salesByDay = Sale::select(DB::raw('DATE(created_at) as date'), DB::raw('SUM(amount) as total_sales'))
    ->groupBy(DB::raw('DATE(created_at)'))
    ->get();

foreach ($salesByDay as $sale) {
    echo "Date: " . $sale->date . " - Total Sales: " . $sale->total_sales . "<br>";
}

These examples illustrate how versatile and powerful the `GroupBy` method is in Laravel, allowing you to aggregate and analyze data efficiently within your application.