Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    51

    Unanswered: Subquery inside a SELECT clause

    I have to include the subquery inside a SELECT

    SELECT A, B, C, D, (SELECT inventory FROM Table_A WHERE ItemID=KPS.ItemID AND FiscalWeek = KPS.FiscalWeek ), E, F

    FROM
    Product as KPS,
    Table1,
    Table2,
    Table3
    WHERE
    --conditions---
    GROUP BY
    KPS.ItemID, KPS. CoID, KPS.FiscalYear

    (GROUP BY doesnt have the Fiscal Week)

    So, I get this error
    Column 'Product.FiscalWeek' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

    I cant include the FiscalWeek in the GROUP BY because that would change the sum
    How can I solve this issue?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you wish to have help on this query, you would need to show the actual query

    the error message does not match the query you posted, because Product.FiscalWeek isn't in the SELECT clause

    neither is any SUM() in the SELECT clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Posts
    51
    here is the query:

    Code:
    SELECT     PTF.TimeFrameId, PTF.TimeFrame_Desc, PTF.TimeFrame_StartDate, PTF.TimeFrame_EndDate, KPS.FiscalYear, KPS.CorpID,
                           KPS.CoID, KPS.DivID, KPS.DeptID, KPS.ClassID, KPS.SubClassID, KPS.StyleID, KPS.StyleColorID, COUNT_BIG(*) AS Count, 
                          SUM(ISNULL(KPS.CP_Cost_Sales_Reg, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Cost_Sales_Reg, SUM(ISNULL(KPS.CP_Cost_Sales_Mkd, 0) 
                          * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Cost_Sales_Mkd, SUM(ISNULL(KPS.CP_Retail_Sales_Reg, 0) * ISNULL(PP.PlanPct, 14.29) / 100) 
                          AS CP_Retail_Sales_Reg, SUM(ISNULL(KPS.CP_Retail_Sales_Mkd, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Retail_Sales_Mkd, 
                          SUM(ISNULL(KPS.CP_Unit_Sales_Reg, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Unit_Sales_Reg, SUM(ISNULL(KPS.CP_Unit_Sales_Mkd, 0) 
                          * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Unit_Sales_Mkd, 
    
    (
    ISNULL((SELECT CP_Unit_TtlInvBOP_Reg FROM tabePlanning WHERE StyleID = KPS.StyleID AND StyleColorID = KPS.StyleColorID AND FiscalWeek = KPS.FiscalWeek 
    AND CorpID = KPS.CorpID AND CoID = KPS.CoID AND  DivID = KPS.DivID AND DeptID = KPS.DeptID AND  ClassID = KPS.ClassID AND  SubClassID = KPS.SubClassID
    ),0)
    ) AS CP_Unit_TtlInvBOP_Reg, 
    SUM(ISNULL(KPS.CP_Unit_TtlInvBOP_Mkd, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Unit_TtlInvBOP_Mkd
    FROM         dbo.tabePlanning AS KPS INNER JOIN
                          dbo.tblTimeFrame_Days AS PTF ON KPS.FiscalYear = PTF.FiscalYear AND KPS.FiscalWeek = PTF.FiscalWeekNbr INNER JOIN
                          dbo.tblPromoEvent AS PE ON PTF.TimeFrame_StartDate = PE.Promotion_StartDate AND 
                          PTF.TimeFrame_EndDate = PE.Promotion_EndDate INNER JOIN
                          dbo.tblPromotion_ProductCC AS PEPCC ON PEPCC.PromoEventID = PE.PromoEventID AND PEPCC.CorpID = KPS.CorpID AND 
                          PEPCC.CoID = KPS.CoID AND PEPCC.DivID = KPS.DivID AND PEPCC.DeptID = KPS.DeptID AND PEPCC.ClassID = KPS.ClassID AND 
                          PEPCC.SubClassID = KPS.SubClassID AND PEPCC.StyleID = KPS.StyleID AND PEPCC.StyleColorID = KPS.StyleColorID INNER JOIN
                          dbo.tblRetailStyle AS RS ON PEPCC.CorpID = RS.Corp_ID AND PEPCC.CoID = RS.Co_ID AND PEPCC.DivID = RS.Div_ID AND 
                          PEPCC.DeptID = RS.Dept_ID AND PEPCC.ClassID = RS.Class_ID AND PEPCC.SubClassID = RS.SubClass_ID AND PEPCC.StyleID = RS.Style_ID AND 
                          PEPCC.StyleColorID = RS.StyleColor_ID INNER JOIN
                          dbo.tablePlanning_Percentage AS PP ON PTF.Promo_CurrentDate = PP.PlanDay
    GROUP BY PTF.TimeFrameId, PTF.TimeFrame_Desc, PTF.TimeFrame_StartDate, PTF.TimeFrame_EndDate, KPS.CorpID, KPS.CoID, 
                          KPS.DivID, KPS.DeptID, KPS.ClassID, KPS.SubClassID, KPS.StyleID, KPS.StyleColorID, KPS.FiscalYear
    And errror is
    Column 'Planning.FiscalWeek' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

  4. #4
    Join Date
    Nov 2004
    Posts
    51
    here is the query:

    Code:
    SELECT     PTF.TimeFrameId, PTF.TimeFrame_Desc, PTF.TimeFrame_StartDate, PTF.TimeFrame_EndDate, KPS.FiscalYear, KPS.CorpID,
                           KPS.CoID, KPS.DivID, KPS.DeptID, KPS.ClassID, KPS.SubClassID, KPS.StyleID, KPS.StyleColorID, COUNT_BIG(*) AS Count, 
                          SUM(ISNULL(KPS.CP_Cost_Sales_Reg, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Cost_Sales_Reg, SUM(ISNULL(KPS.CP_Cost_Sales_Mkd, 0) 
                          * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Cost_Sales_Mkd, SUM(ISNULL(KPS.CP_Retail_Sales_Reg, 0) * ISNULL(PP.PlanPct, 14.29) / 100) 
                          AS CP_Retail_Sales_Reg, SUM(ISNULL(KPS.CP_Retail_Sales_Mkd, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Retail_Sales_Mkd, 
                          SUM(ISNULL(KPS.CP_Unit_Sales_Reg, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Unit_Sales_Reg, SUM(ISNULL(KPS.CP_Unit_Sales_Mkd, 0) 
                          * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Unit_Sales_Mkd, 
    
    (
    ISNULL((SELECT CP_Unit_TtlInvBOP_Reg FROM tabePlanning WHERE StyleID = KPS.StyleID AND StyleColorID = KPS.StyleColorID AND FiscalWeek = KPS.FiscalWeek 
    AND CorpID = KPS.CorpID AND CoID = KPS.CoID AND  DivID = KPS.DivID AND DeptID = KPS.DeptID AND  ClassID = KPS.ClassID AND  SubClassID = KPS.SubClassID
    ),0)
    ) AS CP_Unit_TtlInvBOP_Reg, 
    SUM(ISNULL(KPS.CP_Unit_TtlInvBOP_Mkd, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Unit_TtlInvBOP_Mkd
    FROM         dbo.tabePlanning AS KPS INNER JOIN
                          dbo.tblTimeFrame_Days AS PTF ON KPS.FiscalYear = PTF.FiscalYear AND KPS.FiscalWeek = PTF.FiscalWeekNbr INNER JOIN
                          dbo.tblPromoEvent AS PE ON PTF.TimeFrame_StartDate = PE.Promotion_StartDate AND 
                          PTF.TimeFrame_EndDate = PE.Promotion_EndDate INNER JOIN
                          dbo.tblPromotion_ProductCC AS PEPCC ON PEPCC.PromoEventID = PE.PromoEventID AND PEPCC.CorpID = KPS.CorpID AND 
                          PEPCC.CoID = KPS.CoID AND PEPCC.DivID = KPS.DivID AND PEPCC.DeptID = KPS.DeptID AND PEPCC.ClassID = KPS.ClassID AND 
                          PEPCC.SubClassID = KPS.SubClassID AND PEPCC.StyleID = KPS.StyleID AND PEPCC.StyleColorID = KPS.StyleColorID INNER JOIN
                          dbo.tblRetailStyle AS RS ON PEPCC.CorpID = RS.Corp_ID AND PEPCC.CoID = RS.Co_ID AND PEPCC.DivID = RS.Div_ID AND 
                          PEPCC.DeptID = RS.Dept_ID AND PEPCC.ClassID = RS.Class_ID AND PEPCC.SubClassID = RS.SubClass_ID AND PEPCC.StyleID = RS.Style_ID AND 
                          PEPCC.StyleColorID = RS.StyleColor_ID INNER JOIN
                          dbo.tablePlanning_Percentage AS PP ON PTF.Promo_CurrentDate = PP.PlanDay
    GROUP BY PTF.TimeFrameId, PTF.TimeFrame_Desc, PTF.TimeFrame_StartDate, PTF.TimeFrame_EndDate, KPS.CorpID, KPS.CoID, 
                          KPS.DivID, KPS.DeptID, KPS.ClassID, KPS.SubClassID, KPS.StyleID, KPS.StyleColorID, KPS.FiscalYear
    And errror is
    Column 'Planning.FiscalWeek' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

  5. #5
    Join Date
    Nov 2004
    Posts
    51
    here is the query:

    Code:
    SELECT     PTF.TimeFrameId, PTF.TimeFrame_Desc, PTF.TimeFrame_StartDate, PTF.TimeFrame_EndDate, KPS.FiscalYear, KPS.CorpID,
                           KPS.CoID, KPS.DivID, KPS.DeptID, KPS.ClassID, KPS.SubClassID, KPS.StyleID, KPS.StyleColorID, COUNT_BIG(*) AS Count, 
                          SUM(ISNULL(KPS.CP_Cost_Sales_Reg, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Cost_Sales_Reg, SUM(ISNULL(KPS.CP_Cost_Sales_Mkd, 0) 
                          * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Cost_Sales_Mkd, SUM(ISNULL(KPS.CP_Retail_Sales_Reg, 0) * ISNULL(PP.PlanPct, 14.29) / 100) 
                          AS CP_Retail_Sales_Reg, SUM(ISNULL(KPS.CP_Retail_Sales_Mkd, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Retail_Sales_Mkd, 
                          SUM(ISNULL(KPS.CP_Unit_Sales_Reg, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Unit_Sales_Reg, SUM(ISNULL(KPS.CP_Unit_Sales_Mkd, 0) 
                          * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Unit_Sales_Mkd, 
    
    (
    ISNULL((SELECT CP_Unit_TtlInvBOP_Reg FROM tabePlanning WHERE StyleID = KPS.StyleID AND StyleColorID = KPS.StyleColorID AND FiscalWeek = KPS.FiscalWeek 
    AND CorpID = KPS.CorpID AND CoID = KPS.CoID AND  DivID = KPS.DivID AND DeptID = KPS.DeptID AND  ClassID = KPS.ClassID AND  SubClassID = KPS.SubClassID
    ),0)
    ) AS CP_Unit_TtlInvBOP_Reg, 
    SUM(ISNULL(KPS.CP_Unit_TtlInvBOP_Mkd, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Unit_TtlInvBOP_Mkd
    FROM         dbo.tabePlanning AS KPS INNER JOIN
                          dbo.tblTimeFrame_Days AS PTF ON KPS.FiscalYear = PTF.FiscalYear AND KPS.FiscalWeek = PTF.FiscalWeekNbr INNER JOIN
                          dbo.tblPromoEvent AS PE ON PTF.TimeFrame_StartDate = PE.Promotion_StartDate AND 
                          PTF.TimeFrame_EndDate = PE.Promotion_EndDate INNER JOIN
                          dbo.tblPromotion_ProductCC AS PEPCC ON PEPCC.PromoEventID = PE.PromoEventID AND PEPCC.CorpID = KPS.CorpID AND 
                          PEPCC.CoID = KPS.CoID AND PEPCC.DivID = KPS.DivID AND PEPCC.DeptID = KPS.DeptID AND PEPCC.ClassID = KPS.ClassID AND 
                          PEPCC.SubClassID = KPS.SubClassID AND PEPCC.StyleID = KPS.StyleID AND PEPCC.StyleColorID = KPS.StyleColorID INNER JOIN
                          dbo.tblRetailStyle AS RS ON PEPCC.CorpID = RS.Corp_ID AND PEPCC.CoID = RS.Co_ID AND PEPCC.DivID = RS.Div_ID AND 
                          PEPCC.DeptID = RS.Dept_ID AND PEPCC.ClassID = RS.Class_ID AND PEPCC.SubClassID = RS.SubClass_ID AND PEPCC.StyleID = RS.Style_ID AND 
                          PEPCC.StyleColorID = RS.StyleColor_ID INNER JOIN
                          dbo.tablePlanning_Percentage AS PP ON PTF.Promo_CurrentDate = PP.PlanDay
    GROUP BY PTF.TimeFrameId, PTF.TimeFrame_Desc, PTF.TimeFrame_StartDate, PTF.TimeFrame_EndDate, KPS.CorpID, KPS.CoID, 
                          KPS.DivID, KPS.DeptID, KPS.ClassID, KPS.SubClassID, KPS.StyleID, KPS.StyleColorID, KPS.FiscalYear
    And errror is
    Column 'Planning.FiscalWeek' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm not sure if you changed the query at all during the 3 times you posted it, but i looked only at the last one you posted

    you can't be getting that error message from that query because Planning.FiscalWeek is ~not~ the select list

    by the way, your query is impossible to understand in its present format

    i had to reformat it first --
    Code:
    SELECT PTF.TimeFrameId
         , PTF.TimeFrame_Desc
         , PTF.TimeFrame_StartDate
         , PTF.TimeFrame_EndDate
         , KPS.FiscalYear
         , KPS.CorpID
         , KPS.CoID
         , KPS.DivID
         , KPS.DeptID
         , KPS.ClassID
         , KPS.SubClassID
         , KPS.StyleID
         , KPS.StyleColorID
         , COUNT_BIG(*) AS Count
         , SUM(ISNULL(KPS.CP_Cost_Sales_Reg, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Cost_Sales_Reg
         , SUM(ISNULL(KPS.CP_Cost_Sales_Mkd, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Cost_Sales_Mkd
         , SUM(ISNULL(KPS.CP_Retail_Sales_Reg, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Retail_Sales_Reg
         , SUM(ISNULL(KPS.CP_Retail_Sales_Mkd, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Retail_Sales_Mkd
         , SUM(ISNULL(KPS.CP_Unit_Sales_Reg, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Unit_Sales_Reg
         , SUM(ISNULL(KPS.CP_Unit_Sales_Mkd, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Unit_Sales_Mkd
         , ( ISNULL( ( SELECT CP_Unit_TtlInvBOP_Reg
                         FROM tabePlanning
                        WHERE StyleID = KPS.StyleID
                          AND StyleColorID = KPS.StyleColorID
                          AND FiscalWeek = KPS.FiscalWeek
                          AND CorpID = KPS.CorpID
                          AND CoID = KPS.CoID
                          AND DivID = KPS.DivID
                          AND DeptID = KPS.DeptID
                          AND ClassID = KPS.ClassID
                          AND SubClassID = KPS.SubClassID),0) ) AS CP_Unit_TtlInvBOP_Reg
         , SUM(ISNULL(KPS.CP_Unit_TtlInvBOP_Mkd, 0) * ISNULL(PP.PlanPct, 14.29) / 100) AS CP_Unit_TtlInvBOP_Mkd
      FROM dbo.tabePlanning AS KPS
    INNER
      JOIN dbo.tblTimeFrame_Days AS PTF
        ON KPS.FiscalYear = PTF.FiscalYear
       AND KPS.FiscalWeek = PTF.FiscalWeekNbr
    INNER
      JOIN dbo.tblPromoEvent AS PE
        ON PTF.TimeFrame_StartDate = PE.Promotion_StartDate
       AND PTF.TimeFrame_EndDate = PE.Promotion_EndDate
    INNER
      JOIN dbo.tblPromotion_ProductCC AS PEPCC
        ON PEPCC.PromoEventID = PE.PromoEventID
       AND PEPCC.CorpID = KPS.CorpID
       AND PEPCC.CoID = KPS.CoID
       AND PEPCC.DivID = KPS.DivID
       AND PEPCC.DeptID = KPS.DeptID
       AND PEPCC.ClassID = KPS.ClassID
       AND PEPCC.SubClassID = KPS.SubClassID
       AND PEPCC.StyleID = KPS.StyleID
       AND PEPCC.StyleColorID = KPS.StyleColorID
    INNER
      JOIN dbo.tblRetailStyle AS RS
        ON PEPCC.CorpID = RS.Corp_ID
       AND PEPCC.CoID = RS.Co_ID
       AND PEPCC.DivID = RS.Div_ID
       AND PEPCC.DeptID = RS.Dept_ID
       AND PEPCC.ClassID = RS.Class_ID
       AND PEPCC.SubClassID = RS.SubClass_ID
       AND PEPCC.StyleID = RS.Style_ID
       AND PEPCC.StyleColorID = RS.StyleColor_ID
    INNER
      JOIN dbo.tablePlanning_Percentage AS PP
        ON PTF.Promo_CurrentDate = PP.PlanDay
    GROUP
        BY PTF.TimeFrameId
         , PTF.TimeFrame_Desc
         , PTF.TimeFrame_StartDate
         , PTF.TimeFrame_EndDate
         , KPS.CorpID
         , KPS.CoID
         , KPS.DivID
         , KPS.DeptID
         , KPS.ClassID
         , KPS.SubClassID
         , KPS.StyleID
         , KPS.StyleColorID
         , KPS.FiscalYear
    if you compare the SELECT list with the GROUP BY list, you'll see that they are equivalent (not in the exact same order, but they don't have to be)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and no, you do not "have to include the subquery inside a SELECT".
    Your subquery would be better implemented by a left outer join.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •