Example 6. GROUP BY Raw Expression

Sometimes more than a simple column is needed. For example, we want to group our orders by the month they were created. To do this, we will use a raw expression:

Controller

$orders = Order::selectRaw(
    'month(orders.order_time) as month, sum(order_p.quantity) as total_quantity, sum(orders.total) as order_total, count(distinct id) as total_orders'
)
    ->join(
        DB::raw('(select order_id, sum(quantity) as quantity
             from `order_product`
             group by order_id) as order_p'),
        'order_p.order_id', '=', 'orders.id',
    )
    // We are using the month() mysql function to get the month from the order_time column
    ->groupByRaw('month(orders.order_time)')
    ->orderBy('month')
    ->orderBy('total_orders', 'desc')
    ->get();
 
return view('examples.groupByRawMonthWithEloquent', [
    'orders' => $orders
]);

This produces the following SQL query:

select month(orders.order_time) as month,
       sum(order_p.quantity)    as total_quantity,
       sum(orders.total)        as order_total,
       count(distinct id)       as total_orders
from `orders`
         inner join (select order_id, sum(quantity) as quantity
                     from `order_product`
                     group by order_id) as order_p on `order_p`.`order_id` = `orders`.`id`
group by month(orders.order_time)
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">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">${{ 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>
{{-- ... --}}

This can be used for any raw expression, not just the month. For example, we can group by the day:

Controller

$orders = Order::selectRaw(
    'day(orders.order_time) as day, sum(order_p.quantity) as total_quantity, sum(orders.total) as order_total, count(distinct id) as total_orders'
)
    ->join(
        DB::raw('(select order_id, sum(quantity) as quantity
             from `order_product`
             group by order_id) as order_p'),
        'order_p.order_id', '=', 'orders.id',
    )
    ->groupByRaw('day(orders.order_time)')
    ->orderBy('day')
    ->orderBy('total_orders', 'desc')
    ->get();
 
return view('examples.groupByRawDayWithEloquent', [
    'orders' => $orders
]);

This produces the following SQL query:

select day(orders.order_time) as day,
       sum(order_p.quantity)  as total_quantity,
       sum(orders.total)      as order_total,
       count(distinct id)     as total_orders
from `orders`
         inner join (select order_id, sum(quantity) as quantity
                     from `order_product`
                     group by order_id) as order_p on `order_p`.`order_id` = `orders`.`id`
group by day(orders.order_time)
order by `day` asc, `total_orders` desc

You can find the Automated test example for this lesson in our repository or Month Test here and Day Test here.

Example 5. GROUP BY with ORDER BY Example 7. GROUP BY + ORDER BY: Multiple Columns

Example 6. GROUP BY Raw Expression

Course Content

GroupBy in Eloquent: Syntax Options

GroupBy: More Custom Examples

Laravel GroupBy: Practical Examples

Example 6. GROUP BY Raw Expression

Course Content

GroupBy in Eloquent: Syntax Options

GroupBy: More Custom Examples