Example 7. GROUP BY + ORDER BY: Multiple Columns

In this example, we will show you how to group by a column and order by multiple columns.

For example, we want to group our orders by the month they were created and then order them by the total orders count:

Controller

$orders = Order::selectRaw(
        'month(orders.order_time) as month, sum(order_product.quantity) as total_quantity, sum(orders.total) as order_total, count(*) as total_orders, products.name as product_name'
    )
    ->join('order_product', 'order_product.order_id', '=', 'orders.id')
    ->join('products', 'order_product.product_id', '=', 'products.id')
    // Group by Month first, then by product name
    ->groupByRaw('month(orders.order_time), product_name')
    // Order by month, then by total orders
    ->orderBy('month')
    ->orderBy('total_orders', 'desc')
    ->get();
 
return view('examples.groupByMultipleColumnsEloquent', [
    'orders' => $orders
]);

This produces the following SQL query:

select month(orders.order_time)    as month,
       sum(order_product.quantity) as total_quantity,
       sum(orders.total)           as order_total,
       count(*)                    as total_orders,
       products.name               as product_name
from `orders`
    inner join `order_product` on `order_id` = `orders`.`id`
    inner join `products` on `product_id` = `products`.`id`
group by month(orders.order_time), product_name
order by `month` asc, `total_orders` desc

Then in our view, we can display the columns needed, and they will be grouped by the month:

View

{{-- ... --}}
<table class="table-auto w-full">
    <thead>
    <tr>
        <th class="px-4 py-2">Date</th>
        <th class="px-4 py-2">Product</th>
        <th class="px-4 py-2">Total</th>
        <th class="px-4 py-2">Total Items</th>
        <th class="px-4 py-2">Total Orders</th>
    </tr>
    </thead>
    <tbody>
    @foreach ($orders as $order)
        <tr>
            <td class="border px-4 py-2">{{ $order->month }}</td>
            <td class="border px-4 py-2">{{ $order->product_name }}</td>
            <td class="border px-4 py-2">${{ number_format($order->order_total, 2) }}</td>
            <td class="border px-4 py-2">{{ $order->total_quantity }}</td>
            <td class="border px-4 py-2">{{ $order->total_orders }}</td>
        </tr>
    @endforeach
    </tbody>
</table>
{{-- ... --}}

Example 6. GROUP BY Raw Expression Common Issue with GROUP BY: "Expression is not in GROUP BY..."

Example 7. GROUP BY + ORDER BY: Multiple Columns

Course Content

GroupBy in Eloquent: Syntax Options

GroupBy: More Custom Examples

Laravel GroupBy: Practical Examples

Example 7. GROUP BY + ORDER BY: Multiple Columns

Course Content

GroupBy in Eloquent: Syntax Options

GroupBy: More Custom Examples