Example 3. GROUP BY with Extra Calculations

This example will show you how to group by a column and aggregate data from multiple tables.

For example, we want to display Current Stock, Total Products Moved and Products Sold:

Controller

use App\Models\Product;
use DB;
 
// ...
 
$products = Product::query()
    ->select(['name', 'stock_quantity'])
    ->join('order_product', 'products.id', '=', 'order_product.product_id')
    ->addSelect(DB::raw('SUM(order_product.quantity) + products.stock_quantity as total_quantity'))
    ->groupBy('products.id')
    ->get();
 
return view('examples.groupByAggregateWithCalculations', [
    'products' => $products
]);

This produces the following SQL query:

select `name`, `stock_quantity`, SUM(order_product.quantity) + products.stock_quantity as total_quantity
from `products`
    inner join `order_product` on `products`.`id` = `order_product`.`product_id`
    group by `products`.`id`

Then in our view, we can display the columns needed:

View

{{-- ... --}}
<table class="table-auto w-full">
    <thead>
    <tr>
        <th class="px-4 py-2">Name</th>
        <th class="px-4 py-2">Stock Quantity</th>
        <th class="px-4 py-2">Total Products Moved</th>
        <th class="px-4 py-2">Products Sold</th>
    </tr>
    </thead>
    <tbody>
    @foreach ($products as $product)
        <tr>
            <td class="border px-4 py-2">{{ $product->name }}</td>
            <td class="border px-4 py-2">{{ $product->stock_quantity }}</td>
            <td class="border px-4 py-2">{{ $product->total_quantity }}</td>
            <td class="border px-4 py-2">{{ $product->total_quantity - $product->stock_quantity }}</td>
        </tr>
    @endforeach
    </tbody>
</table>
{{-- ... --}}