How do I perform a GROUP BY on an aliased column in SQL Server?

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?

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

Adding another variant on the answers here to say this is a great use case for Common Table Expressions (CTEs) as well.

WITH CustomersWithFullNames AS (
  SELECT LastName + ', ' + FirstName AS FullName, *
  FROM customers
)
SELECT FullName
FROM CustomersWithFullNames 
GROUP BY FullName

Performance wise, this will work the same as an inner subquery, but (to me at least) reads a little easier in a top down fashion

Side Note: Some databases like snowflake allow you to reference expressions in the projection clause in the group by clause

Answer 2

You can use CROSS APPLY to create an alias and use it in the GROUP BY clause, like so:

SELECT       FullName
FROM         Customers
CROSS APPLY  (SELECT LastName + ', ' + FirstName AS FullName) Alias
GROUP BY     FullName

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

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

Answer 5

Sorry, this is not possible with MS SQL Server (possible though with PostgreSQL):

select lastname + ', ' + firstname as fullname
from person
group by fullname

Otherwise just use this:

select x.fullname
from 
(
    select lastname + ', ' + firstname as fullname
    from person
) as x
group by x.fullname

Or this:

select lastname + ', ' + firstname as fullname
from person
group by lastname, firstname  -- no need to put the ', '

The above query is faster, groups the fields first, then compute those fields.

The following query is slower (it tries to compute first the select expression, then it groups the records based on that computation).

select lastname + ', ' + firstname as fullname
from person
group by lastname + ', ' + firstname

Answer 6

In the old FoxPro (I haven't used it since version 2.5), you could write something like this:

SELECT       LastName + ', ' + FirstName AS 'FullName', Birthday, Title
FROM         customers
GROUP BY     1,3,2

I really liked that syntax. Why isn't it implemented anywhere else? It's a nice shortcut, but I assume it causes other problems?

Answer 7

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 8

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 9

My guess is:

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

Oracle has a similar limitation, which is annoying. I'm curious if there exists a better solution.

To answer the second half of the question, this limitation applies to more complex expressions such as your case statement as well. The best suggestion I've seen it to use a sub-select to name the complex expression.

Answer 10

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;

Answer 11

This is what I do.

SELECT FullName
FROM
(
  SELECT LastName + ', ' + FirstName AS FullName
  FROM customers
) as sub
GROUP BY FullName

---

This technique applies in a straightforward way to your "edit" scenario:

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

Answer 12

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 13

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

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