x

Each GROUP BY expression must contain at least one column that is not an outer reference.

Hi Friends

i am getting above error when i run the below query please help me

     WITH CAL AS(SELECT DISTINCT FOR_MONTH,FOR_YEAR,           
     CASE WHEN @P_CODE IS NULL THEN       
      (SELECT (CONVERT(DECIMAL(10,2),      
             CASE WHEN      
             ( SELECT (SELECT COUNT(*) FROM TERMINATION WHERE P_CODE=D.P_CODE AND FOR_MONTH=TR.FOR_MONTH AND  FOR_YEAR=TR.FOR_YEAR  AND (REASON_TERMINATION='3- Voluntary Consultant Resignation prior to the end of engagement' OR  REASON_TERMINATION='1- Terminated Due to Poor Performance')) +    
             (SELECT COUNT(*) FROM TERMINATION WHERE P_CODE=D.P_CODE AND FOR_MONTH=TR.FOR_MONTH AND  FOR_YEAR=TR.FOR_YEAR AND (REASON_TERMINATION='3- Voluntary Consultant Resignation prior to the end of engagement' OR  REASON_TERMINATION='1- Terminated Due to Poor Performance')) )=0       
                THEN 0 ELSE      
                 (SELECT CONVERT(DECIMAL(10,2),COUNT(*)) FROM TERMINATION WHERE P_CODE=D.P_CODE AND (REASON_TERMINATION='3- Voluntary Consultant Resignation prior to the end of engagement' OR  REASON_TERMINATION='1- Terminated Due to Poor Performance'))*(100)/      
                 (SELECT ( (SELECT AVG(t.TotalHeadCountCurrent) FROM (SELECT COUNT(*) AS TOTALHEADCOUNTCURRENT FROM RESOURCE_QCR167 WHERE REL_LVL='Citigroup' AND P_CODE=D.P_CODE AND FOR_MONTH=TR.FOR_MONTH  AND  FOR_YEAR=TR.FOR_YEAR ) T  ) +   
                (SELECT AVG(R.TOTALHEADCOUNTPRE) FROM ( SELECT COUNT(*) AS TOTALHEADCOUNTPRE FROM RESOURCE_QCR167_HISTORY WHERE REL_LVL='Citigroup' AND P_CODE=D.P_CODE AND FOR_MONTH=TR.FOR_MONTH  AND  FOR_YEAR=TR.FOR_YEAR) R )) 
 )      
          END)))    
     ELSE      
    (SELECT (CONVERT(DECIMAL(10,2),      
             CASE WHEN      
             ( SELECT (SELECT COUNT(*) FROM TERMINATION WHERE P_CODE=D.P_CODE AND FOR_MONTH=TR.FOR_MONTH AND  FOR_YEAR=TR.FOR_YEAR  AND (REASON_TERMINATION='3- Voluntary Consultant Resignation prior to the end of engagement' OR  REASON_TERMINATION='1- Terminated Due to Poor Performance')) +   
             (SELECT COUNT(*) FROM TERMINATION WHERE P_CODE=D.P_CODE AND FOR_MONTH=TR.FOR_MONTH AND  FOR_YEAR=TR.FOR_YEAR AND (REASON_TERMINATION='3- Voluntary Consultant Resignation prior to the end of engagement' OR  REASON_TERMINATION='1- Terminated Due to Poor Performance')) )=0       
                THEN 0 ELSE      
                 (SELECT CONVERT(DECIMAL(10,2),COUNT(*)) FROM TERMINATION WHERE P_CODE=D.P_CODE AND (REASON_TERMINATION='3- Voluntary Consultant Resignation prior to the end of engagement' OR  REASON_TERMINATION='1- Terminated Due to Poor Performance'))*(100)/      
                 (SELECT ( (SELECT AVG(T.[TOTALHEADCOUNTCURRENT]) FROM (SELECT COUNT(*) AS [TOTALHEADCOUNTCURRENT] FROM RESOURCE_QCR167 WHERE REL_LVL='Citigroup' AND P_CODE=D.P_CODE AND FOR_MONTH=TR.FOR_MONTH  AND  FOR_YEAR=TR.FOR_YEAR ) T  GROUP BY FOR_MONTH,FOR_YEAR   ) +   
                 (SELECT AVG(R.[TOTALHEADCOUNTPRE]) FROM ( SELECT COUNT(*) AS [TOTALHEADCOUNTPRE] FROM RESOURCE_QCR167_HISTORY WHERE REL_LVL='Citigroup' AND P_CODE=D.P_CODE AND FOR_MONTH=TR.FOR_MONTH  AND  FOR_YEAR=TR.FOR_YEAR) R  GROUP BY FOR_MONTH,FOR_YEAR   ) )  )      
             END)))      
            
     END AS [ATTRITION]      
     FROM TERMINATION AS TR        
     INNER JOIN DASHBOARD_BILLING AS D ON D.P_CODE=TR.P_CODE AND proj_status<>'Closed project'      
     INNER JOIN #TempList t ON TR.FOR_MONTH = t.OrderID      
     WHERE FOR_MONTH IS NOT NULL      
     AND FOR_YEAR=@FOR_YEAR)      
       
     SELECT FOR_MONTH ,    SUBSTRING(DATENAME(MM,DATEADD(MM,FOR_MONTH-1,0)),0,4)+'-'+SUBSTRING(FOR_YEAR,3,LEN(FOR_YEAR)-1) AS MONTHNAME,       
     [VALUE]=CONVERT(DECIMAL(10,2),([ATTRITION]))   FROM CAL GROUP BY FOR_MONTH,FOR_YEAR
more ▼

asked Sep 19, 2012 at 09:18 AM in Default

avatar image

ravikumar.t
10 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

It means you have an aggregate-subquery (inner query) where all columns in the GROUP BY clause references the outer query.

See this article for a simple example: http://www.a2zmenu.com/Blogs/SQL/Each-GROUP-BY-expression-must-contain-at-least-one-column-that-is-not-an-outer-reference.aspx

(and by all means - use Google to search for the error message before posting questions here)

more ▼

answered Sep 19, 2012 at 09:39 AM

avatar image

Magnus Ahlkvist
22.5k 20 44 43

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2036
x85

asked: Sep 19, 2012 at 09:18 AM

Seen: 1760 times

Last Updated: Sep 19, 2012 at 09:39 AM

Copyright 2018 Redgate Software. Privacy Policy