How do I perform a GROUP BY on an aliased column in MS-SQL Server?
You pass the expression you want to group by rather than the alias
SELECT LastName + ', ' + FirstName AS 'FullName'FROM customersGROUP BY LastName + ', ' + FirstName
This is what I do.
SELECT FullNameFROM( SELECT LastName + ', ' + FirstName AS FullName FROM customers) as subGROUP BY FullNameThis technique applies in a straightforward way to your "edit" scenario:
SELECT FullNameFROM( SELECT CASE WHEN LastName IS NULL THEN FirstName WHEN LastName IS NOT NULL THEN LastName + ', ' + FirstName END AS FullName FROM customers) as subGROUP BY FullName
Unfortunately you can't reference your alias in the GROUP BY statement, you'll have to write the logic again, amazing as that seems.
SELECT LastName + ', ' + FirstName AS 'FullName'FROM customersGROUP BY LastName + ', ' + FirstNameAlternately you could put the select into a subselect or common table expression, after which you could group on the column name (no longer an alias.)