How do I perform a GROUP BY on an aliased column i

Question

I'm trying to perform a group by action on an aliased column (example below) but can't determine the proper syntax.

SELECT       LastName + ', ' + FirstName AS 'FullName'
FROM         customers
GROUP BY     'FullName'

What is the correct syntax?

---

EDIT

Extending the question further (I had not expected the answers I had received) would the solution still apply for a CASEed aliased column?

SELECT       
    CASE
        WHEN LastName IS NULL THEN FirstName
        WHEN LastName IS NOT NULL THEN LastName + ', ' + FirstName
    END AS 'FullName'
FROM         customers
GROUP BY     
    LastName, FirstName

And the answer is yes it does still apply.

Answer 1

You pass the expression you want to group by rather than the alias

SELECT       LastName + ', ' + FirstName AS 'FullName'
FROM         customers
GROUP BY      LastName + ', ' + FirstName

Answer 2

If you want to avoid the mess of the case statement being in your query twice, you may want to place it in a User-Defined-Function.

Sorry, but SQL Server would not render the dataset before the Group By clause so the column alias is not available. You could use it in the Order By.

Answer 3

For anyone who finds themselves with the following problem (grouping by ensuring zero and null values are treated as equals)...

SELECT AccountNumber, Amount AS MyAlias
FROM Transactions
GROUP BY AccountNumber, ISNULL(Amount, 0)

(I.e. SQL Server complains that you haven't included the field Amount in your Group By or aggregate function)

...remember to place the exact same function in your SELECT...

SELECT AccountNumber, ISNULL(Amount, 0) AS MyAlias
FROM Transactions
GROUP BY AccountNumber, ISNULL(Amount, 0)

Answer 4

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         customers
GROUP BY     LastName + ', ' + FirstName

Alternately 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.)

Answer 5

SELECT       
    CASE
        WHEN LastName IS NULL THEN FirstName
        WHEN LastName IS NOT NULL THEN LastName + ', ' + FirstName
    END AS 'FullName'
FROM
    customers
GROUP BY     
    LastName,
    FirstName

This works because the formula you use (the CASE statement) can never give the same answer for two different inputs.

This is not the case if you used something like:

LEFT(FirstName, 1) + ' ' + LastName

In such a case "James Taylor" and "John Taylor" would both result in "J Taylor".

If you wanted your output to have "J Taylor" twice (one for each person):

GROUP BY LastName, FirstName

If, however, you wanted just one row of "J Taylor" you'd want:

GROUP BY LastName, LEFT(FirstName, 1)

Answer 6

Given your edited problem description, I'd suggest using COALESCE() instead of that unwieldy CASE expression:

SELECT FullName
FROM (
  SELECT COALESCE(LastName+', '+FirstName, FirstName) AS FullName
  FROM customers
) c
GROUP BY FullName;