Laravel group by month from created_at column and sum rows multiplied with price is not working

I need to get data from 2 tables where I want to fetch the cost group by month. The 2 tables are

spare_stock_history
(spare_id, amount, created_at)

and

base_inventory
(id, unit_price)

I need to get sum of amount*unit_price at each month by using created_at column of stock history.

I have written the following query

 StockHistory::
        select("spare_stock_history.created_at")
        ->sum("spare_stock_history.amount*base_inventory.unit_price")
        ->join("base_inventory","base_inventory.id", "=","spare_stock_history.spare_id")
        ->where(["spare_stock_history.transaction_type"=>2])
        ->orderBy('created_at')
        ->groupBy(StockHistory::raw('MONTH(spare_stock_history.created_at)'))
        ->get();

But it is giving me following sql error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'spare_stock_history.amount*base_inventory.unit_price' in 'field list' (SQL: select sum(`spare_stock_history`.`amount*base_inventory`.`unit_price`) as aggregate from `spare_stock_history`)

How can I fix this? Thanks in advance

Answer

Use DB:raw to handle raw expressions on your Query Builder.

   StockHistory::
select(["spare_stock_history.created_at", DB::raw("SUM(spare_stock_history.amount * base_inventory.unit_price)")])
    ->join("base_inventory", "base_inventory.id", "=", "spare_stock_history.spare_id")
    ->where("spare_stock_history.transaction_type",2)
    ->orderBy('created_at')
    ->groupBy(DB::raw('MONTH(spare_stock_history.created_at), SUM(spare_stock_history.amount * base_inventory.unit_price)'))
    ->get();