Mastering Group By and Having in Laravel Queries

Want to group your data and filter the groups? Laravel's groupBy and having methods are here to help! Let's dive into how these powerful tools can level up your database queries.

What Are groupBy and having?

• groupBy: This method groups your results based on a column.
• having: This method filters the groups, similar to how where filters individual rows.

Basic Usage

Here's a simple example:

$users = DB::table('users')
    ->groupBy('account_id')
    ->having('account_id', '>', 100)
    ->get();

This query groups users by their account ID and then only shows groups where the account ID is greater than 100.

Real-World Example: Sales Report

Let's say you're building a sales report for a multi-store business. You want to see which stores are performing well:

use Illuminate\Support\Facades\DB;

class SalesController extends Controller
{
    public function getTopPerformingStores()
    {
        return DB::table('sales')
            ->select('store_id', DB::raw('SUM(amount) as total_sales'))
            ->groupBy('store_id')
            ->having('total_sales', '>', 10000)
            ->orderBy('total_sales', 'desc')
            ->get();
    }
}

This query:

• Sums up sales for each store
• Groups the results by store
• Only shows stores with over $10,000 in sales
• Orders the results from highest to lowest sales

Using groupBy and having in Laravel lets you slice and dice your data in powerful ways. Whether you're building reports, analyzing trends, or filtering grouped data, these methods have got your back.

If this guide was helpful to you, subscribe to my daily newsletter and give me a follow on X/Twitter. It helps a lot!