How to sum up table rows group by storage_id and product

I am trying the following problem : I am sharing the table image first(from where the rows will be summed up)

When the api is Hit, It should first group the storage_id, then product_id and then sum up quanitity of same product_id

for example :

there are two storage_id : 1 & 2 , two different product_id : 1 & 2 . Now the output should be sum all the rows where storage_id, where product_id. there should be 3 JSON outputs, for storage 1 & product 1, for storage 1 & product 2. for storage 2 & product 2. And they should be then stored in a table like the following table

My code so far ( from the controller ) :

$storages = Storage::get(); // getting storage list

foreach($storages as $storage => $val){
    $storage_id[] =  $storages[$storage]->id; // Getting array of id only
    $get_storage[] = Storage::with('storageProducts')->whereIn('id', $storage_id)->get();

    foreach($get_storage as $product => $val){

         foreach($val as $d){
            $a[$d->id] = StorageProduct::where('transfer_type',1)
                         ->where('storage_id',$d->id)
                         ->groupBy('storage_id', 'product_id')
                         ->sum('quantity');
         }
    }
}

The output I am getting :

{
    "1": 28,
    "2": 23
}

CodePudding user response：

$result = StorageProduct::where('transfer_type',1)
    ->select('storage_id', 'product_id', DB::raw('SUM(quantity) as stock'))
    ->groupBy('storage_id', 'product_id')
    ->get();

CodePudding user response：

SELECT storage_id , product_id ,SUM(stock) as stock FROM tb_stock GROUP BY product_id , storage_id