Declare variable

问题:

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!

回答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