Group By Column Alias

I want to group an sql statement by a column alias. In essense, I want the below to function as it should logically, but grouping by a column created with an as is not allowed. (In

Solution 1:

For the immediate problem of the grouping, you need to either group by the same expression or calculation of your new column, or use it from a derived table.

SELECT MonthYear
FROM (  SELECT Columns,
            CASEWHENDateISNULLTHEN'EMPTY'ELSECASEWHENDate='1/1/1753'THEN'UNAVAILABLE'ELSECAST(MONTH(Date) asvarchar(2))+'/'+CAST(YEAR(Date) asvarchar(4))
                ENDENDAS MonthYear
        FROM tbltablename) T
GROUPBY MonthYear

On the other hand, you shouldn't use VARCHAR(MAX) if its not necessary.

Solution 2:

Your questions says "variable", but I think you mean column alias. Just FYI.

Using the actual column definition should work fine. Untested, but this should do what you need:

SELECTCASEWHENDateISNULLTHEN'EMPTY'ELSECASEWHENDate='1/1/1753'THEN'UNAVAILABLE'ELSECAST(MONTH(Date) asvarchar(MAX))+'/'+CAST(YEAR(Date) asvarchar(MAX))
        ENDENDAS MonthYear
FROM tbltablename
GROUPBYCASEWHENDateISNULLTHEN'EMPTY'ELSECASEWHENDate='1/1/1753'THEN'UNAVAILABLE'ELSECAST(MONTH(Date) asvarchar(MAX))+'/'+CAST(YEAR(Date) asvarchar(MAX))
        ENDEND