Merge records in one query

To merge the records based on the type and company, you can use the GROUP BY clause in your SQL query along with an aggregate function like SUM() to add up the values of the columns that you want to merge. Then, you can pass the result to the view in the controller. Here's an example:

Controller

public function index(): View
{
    $wines = Beverage::selectRaw('type_id, company_id, SUM(amount) as total_amount, SUM(price) as total_price, MIN(created_at) as created_at')
        ->groupBy(['type_id', 'company_id'])
        ->get();
    return view('wine.index', ['wines' => $wines]);
}

In this example, we are using the selectRaw() method to write a raw SQL query that groups the records by type_id and company_id, and uses the SUM() function to merge the amount and price columns. We also use the MIN() function to get the earliest created_at value for each group of records.

Then, we use the groupBy() method to group the records based on type_id and company_id, and the get() method to retrieve the result.

Blade

@foreach ($wines as $wine)
    <tr>
        <td class="whitespace-nowrap py-4 pl-6 pr-3 text-sm font-medium text-gray-900">
            {{ $wine->type->name }}
        </td>
        <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">
            {{ $wine->company->name }}
        </td>
        <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">
            {{ $wine->total_amount }}
        </td>
        <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">
            {{ $wine->total_price }}
        </td>
        <td class="whitespace-nowrap px-3 py-4 text-sm text-gray-500">
            {{ $wine->created_at }}
        </td>
        <td class="relative whitespace-nowrap py-4 pl-3 pr-6 text-right text-sm font-medium">
            <a href="#" class="text-emerald-600 hover:text-emerald-900">Bearbeiten<span class="sr-only">, Lindsay Walton</span></a>
        </td>
    </tr>
@endforeach

In the blade file, you can access the merged values using the names that you gave them in the selectRaw() method, like $wine->total_amount and $wine->total_price. You can also use the type and company relationships to get the names of the corresponding models.

As per your original question example this query would be like the below:

SELECT yyy, zzz, 12, SUM(12.00), SUM(12.00) 
FROM your_table 
GROUP BY yyy, zzz, 12;