covert raw mysql query to yii2 query 2 left join subquery and sum all

The main issue with your code is, that you are calling all() method in your subqueries. Thanks to that, you are passing the results of them into the other query instead of passing them as query.

The other problem is that in your php code you are using leftJoin but in your raw SQL you have JOIN which means INNER JOIN.

You need to use arrays when specifing multiple columns by groupBy().

You might want to wrap the more complex expressions in \yii\db\Expression instance to prevent Yii from messing your expression when attempting to quote table names or columns.

You can also add alias to ActiveQuery by using $shipHeadQuery->alias('sh') instead of $shipHeadQuery->from(ShipHead::tableName() .' AS sh').

You can use arrays to specify the alias for selected columns for better readability.

The whole query might look like this

//Add following two lines to the other use clauses if they are not already there.
use \yii\db\Expression; 
use \yii\db\Query;

$shipContainerQuery = ShipContainer::find()
    ->select([
        'teu' => new Expression('SUM(RC_TEU)'),
        'capacity' => new Expression('SUM(RC_Cubiccapacity)'),
        'ship_head_id',
        'JC_ContainerMode',
        'JC_ContainerNum',
    ])->groupBy(['ship_head_id', 'JC_ContainerNum', 'JC_ContainerMode']);

$shipPackQuery = ShipPack::find()
    ->select([
        'volume' => new Expression('SUM(JL_ActualVolume)'),
        'weight' => new Expression('SUM(JL_ActualWeight)'),
        'ship_head_id',
        'JC_ContainerNum',
    ])->groupBy(['ship_head_id', 'JC_ContainerNum']);

$shipHeadQuery = ShipHead::find()
    ->select([
        'shipment' => 'sh.JS_UniqueConsignRef',
        'average_volume_cbm' => new Expression('SUM(sp.volume)'),
        'average_value' => new Expression('SUM(sp.volume) / SUM(sc.teu)'),
        'average_value_percent' => new Expression('SUM(sp.volume) / SUM(sc.capacity)'),
        'average_weight' => new Expression('SUM(sp.weight)'),
        'month_name' => new Expression("date_format(sh.JS_E_ARV, '%b')"),
        'months_order' => new Expression('if(month(sh.JS_E_ARV) >= month(now()),  month(sh.JS_E_ARV),month(sh.JS_E_ARV) + 12)'),
    ])->alias('sh')
    ->innerJoin(['sc' => $shipContainerQuery], 'sc.ship_head_id = sh.sh_id')
    ->innerJoin(['sp' => $shipPackQuery], 'sp.ship_head_id = sh.sh_id')
    ->where(['sh.is_latest' => 1])
    ->andWhere('sp.JC_ContainerNum = sc.JC_ContainerNum')
    ->andWhere(['in', 'sc.JC_ContainerMode', ['FCL', 'FTL']])
    ->andWhere(new Expression('sh.JS_E_ARV < NOW() - INTERVAL 1 MONTH'))
    ->andWhere(new Expression('sh.JS_E_ARV > Date_add(NOW() - INTERVAL 1 MONTH, interval - 12 month)')
    ->groupBy('sh.sh_id');

$shipQuery = new Query()
    ->select([
        'ship.month_name',
        'minimum_container_utilization' => new Expression('MIN(ship.average_value_percent)'),
        'maximum_container_utilization' => new Expression('MAX(ship.average_value_percent)'),
        'volume' => new Expression('SUM(ship.average_value) / COUNT(ship.shipment)'),
        'volume_kgs' => new Expression('SUM(ship.average_weight) / COUNT(ship.shipment)'),
        'volume_cbm' => new Expression('SUM(ship.average_volume_cbm) / COUNT(ship.shipment)'),
        'volume_percent' => new Expression('SUM(ship.average_value_percent) / COUNT(ship.shipment)'),
    ])->from(['ship' => $shipHeadQuery])
    ->groupBy(['ship.month_name', 'ship.months_order'])
    ->orderBy(['ship.months_order' => SORT_ASC]);

Then you can get the results by calling $shipQuery->all() or you can use the query where you need.

Because I don't have the DB structure I didn't test the query. There might be some typos or the query might need some debugging.

I suggest you just using DAO to remove complexity, converting to yii2 query is not a good idea.