Aggregate Error

• Sql Junkie-204042

  Hall of Fame

  Points: 3874

  More actions

  February 6, 2007 at 3:07 pm

  #116113

  I have a query that does a SUM as follows:

  DECLARE @AP TABLE (ID int, Descrip varchar(50), SuggestedJobQty int)

  DECLARE @APR TABLE (ID int, APID int, Active bit)

  DECLARE @APS TABLE (ID int, APRID int, SetupTime int, RunTime int, Status bit)

  INSERT INTO @AP VALUES (12, ' Descrip of 12' , 10)

  INSERT INTO @APR

  SELECT 15,12, 0

  UNION

  SELECT 16,12, 0

  UNION

  SELECT 17,12, 0

  INSERT INTO @APS

  SELECT 50, 17, 5, 1, 0

  UNION

  SELECT 51, 17, 25, 0, 0

  UNION

  SELECT 52, 17, 35, 2, 0

  UNION

  SELECT 53, 17, 45, 5, 0

   

  SELECT ap.ID, ap.Descrip, ap.SuggestedJobQty

  , (SELECT TOP 1 ID FROM @APR apr WHERE apr.APID = ap.ID AND apr.Active = 0 ORDER BY ID DESC ) APRID

   , (SELECT SUM(aps.SetupTime + (aps.RunTime * ap.SuggestedJobQty)) FROM @APS aps WHERE aps.Status = 0 AND

   aps.APRID = (SELECT TOP 1 ID FROM @APR apr WHERE apr.APID = ap.ID AND apr.Active = 0 ORDER BY ID DESC ))

  FROM @AP ap

  I get an error "Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression." The red text is generating the error.

  Your thoughts on the error and how do I fix this.

  Thanks.

• Daryl AZ

  Hall of Fame

  Points: 3087

  More actions

  February 6, 2007 at 3:18 pm

  #688157

  My 2 cents would to be remove the queries from the select and instead but them into derived tables

  select ap.id, ap.descrip. ap.suggestedJobQty

  , apr.yada

  , aps.yada

  FROM @ap

  INNER JOIN

  ( select yada FROM @aps

  group by yada

  ) aps ON @ap.id = aps.apid

  INNER JOIN

  ( select yada from @apr

  group by yada

  ) apr on @ap_id = apr.apid