How to Group Query Results with `groupBy()`

Table Of Contents

• The Aggregation Challenge
• Laravel GroupBy with Aggregate Functions
• Advanced GroupBy Techniques

The Aggregation Challenge

Need to count posts per category or calculate monthly sales totals? Raw queries return individual records, but groupBy() transforms them into meaningful summaries:

// Without groupBy - Gets all individual posts (not useful for counts)
$allPosts = Post::select('category_id')->get();
// Returns: [{category_id: 1}, {category_id: 1}, {category_id: 2}, ...]

// With groupBy - Gets post counts per category
$postsByCategory = Post::select('category_id', DB::raw('COUNT(*) as posts_count'))
    ->groupBy('category_id')
    ->get();
// Returns: [{category_id: 1, posts_count: 15}, {category_id: 2, posts_count: 8}]

// Even better - Include category names
$categoriesWithCounts = Post::join('categories', 'posts.category_id', '=', 'categories.id')
    ->select('categories.name', DB::raw('COUNT(posts.id) as posts_count'))
    ->groupBy('categories.id', 'categories.name')
    ->orderBy('posts_count', 'desc')
    ->get();

Laravel GroupBy with Aggregate Functions

Combine groupBy with SQL aggregate functions for powerful data analysis:

// Sales analytics - Revenue by month
$monthlySales = Order::select([
        DB::raw('YEAR(created_at) as year'),
        DB::raw('MONTH(created_at) as month'),
        DB::raw('COUNT(*) as total_orders'),
        DB::raw('SUM(total) as total_revenue'),
        DB::raw('AVG(total) as average_order_value')
    ])
    ->where('status', 'completed')
    ->groupBy(DB::raw('YEAR(created_at)'), DB::raw('MONTH(created_at)'))
    ->orderBy('year', 'desc')
    ->orderBy('month', 'desc')
    ->get();

// User engagement - Posts and comments by user
$userActivity = User::leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->leftJoin('comments', 'users.id', '=', 'comments.user_id')
    ->select([
        'users.id',
        'users.name',
        DB::raw('COUNT(DISTINCT posts.id) as posts_count'),
        DB::raw('COUNT(DISTINCT comments.id) as comments_count'),
        DB::raw('(COUNT(DISTINCT posts.id) + COUNT(DISTINCT comments.id)) as total_activity')
    ])
    ->groupBy('users.id', 'users.name')
    ->having(DB::raw('COUNT(DISTINCT posts.id) + COUNT(DISTINCT comments.id)'), '>', 0)
    ->orderBy('total_activity', 'desc')
    ->get();

// E-commerce - Product performance by category
$categoryPerformance = Product::join('order_items', 'products.id', '=', 'order_items.product_id')
    ->join('categories', 'products.category_id', '=', 'categories.id')
    ->select([
        'categories.name as category_name',
        DB::raw('COUNT(DISTINCT products.id) as products_sold'),
        DB::raw('SUM(order_items.quantity) as total_quantity'),
        DB::raw('SUM(order_items.quantity * order_items.price) as total_revenue')
    ])
    ->groupBy('categories.id', 'categories.name')
    ->orderBy('total_revenue', 'desc')
    ->get();

Advanced GroupBy Techniques

Handle complex grouping scenarios and edge cases:

// Multiple grouping levels - Daily, weekly, monthly stats
$dailyStats = PageView::select([
        DB::raw('DATE(created_at) as date'),
        DB::raw('COUNT(*) as views'),
        DB::raw('COUNT(DISTINCT user_id) as unique_visitors')
    ])
    ->where('created_at', '>=', now()->subDays(30))
    ->groupBy(DB::raw('DATE(created_at)'))
    ->orderBy('date', 'desc')
    ->get();

// Conditional aggregation with CASE statements
$userTypes = User::select([
        DB::raw('CASE 
            WHEN created_at >= "' . now()->subDays(30) . '" THEN "New"
            WHEN created_at >= "' . now()->subDays(365) . '" THEN "Regular"
            ELSE "Veteran"
        END as user_type'),
        DB::raw('COUNT(*) as count'),
        DB::raw('AVG(DATEDIFF(NOW(), created_at)) as avg_days_since_signup')
    ])
    ->groupBy(DB::raw('CASE 
        WHEN created_at >= "' . now()->subDays(30) . '" THEN "New"
        WHEN created_at >= "' . now()->subDays(365) . '" THEN "Regular"
        ELSE "Veteran"
    END'))
    ->get();

// Having clause for filtering grouped results
$popularTags = Post::join('post_tag', 'posts.id', '=', 'post_tag.post_id')
    ->join('tags', 'post_tag.tag_id', '=', 'tags.id')
    ->select('tags.name', DB::raw('COUNT(*) as usage_count'))
    ->groupBy('tags.id', 'tags.name')
    ->having('usage_count', '>=', 10) // Only tags used 10+ times
    ->orderBy('usage_count', 'desc')
    ->get();

// Performance tip: Use indexes on grouped columns
// CREATE INDEX idx_posts_category_created ON posts(category_id, created_at);

// Geographic grouping - Orders by country/state
$ordersByLocation = Order::join('users', 'orders.user_id', '=', 'users.id')
    ->join('addresses', 'users.id', '=', 'addresses.user_id')
    ->select([
        'addresses.country',
        'addresses.state',
        DB::raw('COUNT(orders.id) as total_orders'),
        DB::raw('SUM(orders.total) as total_revenue')
    ])
    ->where('addresses.type', 'billing')
    ->groupBy('addresses.country', 'addresses.state')
    ->orderBy('total_revenue', 'desc')
    ->get();

// Collection groupBy for in-memory grouping (use sparingly)
$users = User::with('posts')->get();
$usersByRole = $users->groupBy('role'); // Groups collection by role field

// Better approach - Database-level grouping
$usersByRoleDB = User::select('role', DB::raw('COUNT(*) as count'))
    ->groupBy('role')
    ->get();

Always include grouped columns in your SELECT statement or you'll get SQL errors in strict mode. Use having() instead of where() to filter grouped results, and consider database indexes on frequently grouped columns for better performance.

Related: Laravel Collections: Beyond Basic Array Operations | Database Design: Fundamentals of Good Database Architecture | Database Design Patterns: Complete Guide 2025 | SQL Query Optimization: Indexing, Joins & Performance | Data Analytics: Working with Big Data 2025