How to put MAX() inside AVG() in SQL

Question

So I am having SQL error: "Cannot perform an aggregate function on an expression containing an aggregate or a subquery." I know you will ask me to just put it on a variable but this piece of code is inside a view. SQL does not allow us to put variable inside a view.

SELECT top 1000
              ProductID, 
              (
                NULLIF(
                  AVG(
                    CASE WHEN QtyShip_RAW_adjusted >= 0 and QtyShip_RAW_adjusted < MAX(QtyShip_RAW_adjusted) THEN QtyShip_RAW_adjusted END
                  )/COUNT(*), 
                  0
                ) + SUM(
                  CASE WHEN QtyShip_RAW_adjusted >= 0 THEN QtyShip_RAW_adjusted END
                ) - (
                  SELECT 
                    MAX(QtyShip_RAW_adjusted) 
                  FROM 
                    QtyShip_RAW_adjusted_test
                ) + (
                  SELECT 
                    TOP 1 QtyShip_RAW_adjusted 
                  FROM 
                    (
                      SELECT 
                        TOP 3 QtyShip_RAW_adjusted 
                      FROM 
                        QtyShip_RAW_adjusted_test 
                      ORDER BY 
                        QtyShip_RAW_adjusted DESC
                    ) AS Comp 
                  ORDER BY 
                    QtyShip_RAW_adjusted ASC
                )
              ) / COUNT(
                CASE WHEN QtyShip_RAW_adjusted >= 0 THEN 1 END
              ) as AverageLessPeakWeek 
            FROM 
                QtyShip_RAW_adjusted_test
            GROUP BY 
              ProductID

I already tried putting in a derived table.

FROM 
(
                  SELECT 
                    MAX(QtyShip_RAW_adjusted) as MaxQty
                  FROM 
                    QtyShip_RAW_adjusted_test
) t

But putting it inside derived tables gave me more errors.

                    CASE WHEN QtyShip_RAW_adjusted >= 0 and QtyShip_RAW_adjusted < t.MaxQty THEN QtyShip_RAW_adjusted END

I already made some google search and nothing seems to be working. Thanks Guys!

Answer 1

So I still added the derived table

            (SELECT ProductID, QtyShip_RAW_adjusted,
            (CASE WHEN QtyShip_RAW_adjusted >= 0 and QtyShip_RAW_adjusted < (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 >= 0 and QtyShip_RAW_adjusted < 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