How do I perform a GROUP BY on an aliased column in MS-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?

---

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

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 3

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 4

Good time to use DISTINCT:

SELECT  DISTINCT    LastName + ', ' + FirstName AS 'FullName'
FROM         customers

For the new edit, this would be the best alternative:

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

Answer 5

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 6

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 7

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 8

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 9

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 10

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.