GroupBy Having Query Builder Example in Laravel

In this article, I will show you how to use group by in Laravel. Before we go straight to example, we should know what of the purpose of using group by function in Laravel. GroupBy is a function to group result base to columns with aggregated function.

GroupBy() is usually to aggregated many functions such as MAX(), MIN(), SUM(), COUNT(), AVG() to group result in one or more result. For example I have many users mix with male and female and I want to know how many user are female and male. In this case we will use COUNT() and GroupBy() function to count reference by grouping the results.

We will show various examples with eloquent in Laravel and raw SQL for who familar with SQL Statement. So, let's see how to use group by in laravel 6, laravel 7, and laravel 8 or laravel 9 with where clause.

SQL STATEMENT

SELECT column_names
FROM table_name
WHERE condition 
GROUP BY column_names

Laravel GroupBy Query

To count each gender existing in table Student

DB::table('Student')
    ->select(DB::raw('gender'), DB::raw('count(gender) as number'))
    ->groupBy('gender')
    ->get();

Sample Output:

Before

After

Laravel GroupBy with Having Query

In case we want to filter out what we have grouping, having clause work similar as where clause. Let's me explain a little bit where vs having clause. Basically, where can be use with group by as well as having. where is use for filter out record before work on grouping that's why in raw SQL you will see where clause is written before group by . In contrast, having clause will filter after grouping is ready and can work with aggreated data. I hope you will understand.

DB::table('Mark')
    ->select(DB:raw('student_id'),DB:raw('SUM(score) as total'))
    ->groupBy('student_id')
    ->having('total', '>', 60)
    ->get();

Sample Output:

Before

After

Laravel GroupBy with Where and Having Query

Example how we use where combine with having clause with grouping result.

DB::table('Mark')
    ->select(DB:raw('student_id'),DB:raw('SUM(score) as total'))
    ->where('score','>',35)
    ->groupBy('student_id')
    ->having('total', '>', 60)
    ->get();

Sample Output:

Before

After

The query will look over the records which have score greater than 35, so the result would be 3 records then we sum the score in remain ing records, thus the result is only one record display.

You might also like...

• How to Get List Files in Laravel Directory

• Eloquent Order By Query Example in Laravel

• Autocomplete Search Ajax Typeahead from Database in Laravel

• Laravel - How to Get Current Previous URL in Controller or View

• Laravel Create Custom Artisan Command