In this article, we will see group by query in laravel 9. Here, we will learn how to use group by query in laravel 9. As you might expect, groupBy and having methods may be used to group the query results. learn how to create a collection of data using the laravel groupBy method. It can allow us to group the data by type, id, date, etc. Also, you can group by count and group by multiple columns.

Also, we will give you an example of a laravel 9 group by with where condition and query of the group by in MySQL. The GROUP BY statement is often used with aggregate functions COUNT(), MAX(), MIN(), SUM(), AVG() to group the result set by one or more columns.

So, let's see the laravel 9 group by query, how to use group by in laravel 9, laravel 9 group by with where condition and laravel 9 group by multiple columns.

MySQL Group By Syntax with where condition.

SELECT column_names
FROM table_name
WHERE condition
GROUP BY column_names
ORDER BY column_names;

Example:

SELECT COUNT(user_id), country
FROM users
GROUP BY country;

 

 

The “groupBy” function belongs to the collection, not an eloquent category.

use App\User;
use DB;

public function index()
{
    $users = User::select("*", DB::raw("count(*) as user_id"))
                    ->groupBy('country')
                    ->get();
}

 

The having method's signature is similar to that of the where method.

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

 

Now, we will give examples of the group by date.

DB::table('users')
      ->select(DB::raw('DATE(created_at) as date'), DB::raw('count(*) as views'))
      ->groupBy('date')
      ->get();

In this example, we will group by year with the help of the DB::raw function.

$users = User::select("*", DB::raw("count(*) as user_count"))
                        ->groupBy(DB::raw("year(created_at)"))
                        ->get();

 

---

You might also like:

• Read Also: Laravel 8 Left Join Query Example
• Read Also: Laravel whereBetween Query Example
• Read Also: Laravel whereHas and orWhereHas Query Example
• Read Also: How To Get Current Month Records In MySQL Query