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

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 2

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 3

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 4

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 5

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 6

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