How to put MAX() inside AVG() in SQL
So I still added the derived table
            (SELECT ProductID, QtyShip_RAW_adjusted,
            (CASE WHEN QtyShip_RAW_adjusted &gt;= 0 and QtyShip_RAW_adjusted &lt; (SELECT 
                    MAX(QtyShip_RAW_adjusted) 
                  FROM 
                    QtyShip_RAW_adjusted_test) THEN QtyShip_RAW_adjusted END) MaxQty                    
                  FROM 
                    QtyShip_RAW_adjusted_test) as Datum

As you can see I included the
                    CASE WHEN QtyShip_RAW_adjusted &gt;= 0 and QtyShip_RAW_adjusted &lt; MAX(QtyShip_RAW_adjusted) THEN QtyShip_RAW_adjusted END
                  )/COUNT(*)

and put the alias in the avg()
                NULLIF(
                  AVG(MaxQty)/COUNT(*), 
                  0
                )

Doesn't look good but its working