In this article, we will learn about the laravel group by and how to do it using eloquent, but before moving forward let’s see what is a group by in SQL.
SQL groupby statement
GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”.
GROUP BY the statement is often used with aggregate functions (
AVG()) to group the result-set by one or more columns.
Now we will understood this grouby statement by using the below example.
Let’s Suppose I have table Named as customer’s table which have the below data, and we have to apply group by on the basis of the country.
|1||Alfreds Futterkiste||Maria Anders||Obere Str. 57||Berlin||12209||Germany|
|2||Ana Trujillo Emparedados y helados||Ana Trujillo||Avda. de la Constitución 2222||México D.F.||05021||Mexico|
|3||Antonio Moreno Taquería||Antonio Moreno||Mataderos 2312||México D.F.||05023||Mexico|
|4||Around the Horn||Thomas Hardy||120 Hanover Sq.||London||WA1 1DP||UK|
|5||Berglunds snabbköp||Christina Berglund||Berguvsvägen 8||Luleå||S-958 22||Sweden|
We will write the below code to get the group based on the country.
$customerCount = Customer::groupBy('country')->select('country', DB::raw('count(*) as total'))->get();
If you don’t want to use laravel eloquent and you like to db table, then I thought the below command is beneficial and the below code is useful for you.
$customerCount= DB::table('customers') ->select('country', DB::raw('count(*) as total')) ->groupBy('country') ->get();
Now I thought you have the basic idea of working with laravel group by if you believe I am able to resolve your problem then rate me 5.