Declare variable

Question

I get an error message:

Msg 164, Level 15, State 1, Line 18
Each GROUP BY expression must contain at least one column that is not an outer reference

from this T-SQL code:

DECLARE @CLIENT_COUNT INT

SELECT @CLIENT_COUNT = COUNT(CLT_NBR) FROM CLIENT

SELECT 
    CASE 
       WHEN STATUS = 3 
          THEN 'CATEGORY1' 
          ELSE 'CATEGORY2' 
    END AS Category,
    COUNT(*) AS COUNT,
    @CLIENT_COUNT as [Total CLIENT],
    COUNT(*) / @CLIENT_COUNT as PERCENTAGE
FROM 
    CLIENT_STATUS
WHERE 
    STATUS IN (3, 8)
GROUP BY 
    STATUS, @CLIENT_COUNT

Can you help me to fix it?

Thank you!

Andrei Hirsu · Accepted Answer · 2016-04-19 20:07:21Z

1

Your syntax is wrong. You need to put all of the values that need to be returned before FROM.

DECLARE @CLIENT_COUNT INT

SELECT @CLIENT_COUNT = COUNT(CLT_NBR)
FROM CLIENT

SELECT CASE 
    WHEN STATUS = 3
        THEN 'CATEGORY1'
    ELSE 'CATEGORY2'
    END AS Category
     , COUNT(STATUS) AS StatusCount
     , @CLIENT_COUNT AS [Total CLIENT]
     , COUNT(STATUS) / @CLIENT_COUNT AS PERCENTAGE
FROM CLIENT_STATUS
WHERE STATUS IN (
       3
      ,8
    )
GROUP BY STATUS

edited Apr 19, 2016 at 20:07

answered Apr 19, 2016 at 19:29

Andrei Hirsu

6964 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ice Over a year ago

Thank you Andrei! I just changed my question, can you please help me?

Andrei Hirsu Over a year ago

Updated the answer

Ice Over a year ago

I still got the same error after I add @CLIENT_COUNT IN GROUP BY

Andrei Hirsu Over a year ago

Change the name of the second returned column, Count is a reserved word, Try StatusCount

Collectives™ on Stack Overflow

Declare variable

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related