How do I perform a GROUP BY on an aliased column in SQL Server?
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.
You pass the expression you want to group by rather than the alias
SELECT LastName + ', ' + FirstName AS 'FullName'
FROM customers
GROUP BY LastName + ', ' + FirstName
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
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.)
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