0

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!

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.