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.

Solution 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

Solution 2

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

Solution 3

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

Solution 4

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

Solution 5

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.

View more solutions

Comments

[Bill Karwin]

FWIW you can also use column aliases in GROUP BY in MySQL. But strictly, it isn't supported by the SQL standard.

[Alexandre Leites]

no need to put the ', ' in GROUP BY. the grouping can be indicated with or without the ', ', you can even avoid the typo (e.g. ','), so no invalid query error could arise

[Alexandre Leites]

no need to put the ', ' in GROUP BY. the grouping can be indicated with or without the ', ', you can even avoid the typo (e.g. ','), so no invalid query error could arise

[cmsjr]

Are you referring to my suggestion, or to removing the + ', ' + from the group by expression?

[Alexandre Leites]

just remove the ', ' in group by clause. this works: select lastname + ', ' + firstname as fullname from person group by lastname, firstname

[Jon Ericson]

True. But I find the above a touch more clear. Your sub-select solution is even clearer, obviously. ;-)

[ConcernedOfTunbridgeWells]

You should leave it in, at least to differentiate between 'x, yz' and 'xy, z' which would roll up to the same string without the comma.

[MatBailie]

@NXC: "GROUP BY LastName + FirstName" can fall into the trap you explain, but you can't group by that and have "LastName + ', ' + FirstName" in the SELECT. Using "GROUP BY LastName, FirstName", however, would be fine. (See my comment on the poster's question.)

[Shane Delmore]

This is still possible but is generally frowned upon. Developers end up adding a new column to the middle of your column list and then unexpectedly changing the grouping criteria. It's one of those things that works but is considered poor form, like putting select * from in your queries.

[Alexandre Leites]

+1 Dems GROUP BY Lastname, Firstname would be just fine. Explanation here ienablemuch.com/2010/04/some-wrong-voted-answer-on.html

[Josien]

Check out this answer stackoverflow.com/a/497268/249353 to see why this way of grouping is not preferable.

[Yaroslav]

Asked 3 years ago, accepted answer with 14 upvotes 3 years ago too.

[Eric J.]

You need the comma to avoid the edge case 'ab, c' vs 'a,bc'. They would roll up the same without the comma.

[Alexandre Leites]

@ConcernedOfTunbridgeWells After six years, I now realized where the confusion from my suggestion to remove the ', ' come from, when I said "just remove the ', ' in group by clause", what I mean is just remove the concatenation from the GROUP BY, and then just group things by their entity, which is lastname and firstname, and not removing the concatenation from SELECT; in fact I mentioned in second sentence what I wanted to achieve.

[Alexandre Leites]

@EricJ. What I mean on my first comment is remove the concatenation entirely from GROUP BY, not suggesting to merely remove the string ', ' and then keep the concatenation. There's no edge case even when you remove the concatenation entirely on GROUP BY. My first comment (merely remove the string ', ') on your answer is confusing, it looks like I'm suggesting to keep the concatenation on GROUP BY and just remove the string ', ', but that's not what I mean, sorry for that confusion. Slow performance of GROUP BY concatenation: ienablemuch.com/2010/04/some-wrong-voted-answer-on.html

[Tschallacka]

Same answer as accepted answer, but this one has useful comments. Maybe add that in your answer to make your answer distinctive from the accepted one? and help people like me along when we are forced to work with legacy stuff.

[mpag]

@ShaneDelmore I know this is an ancient thread, but which engines support this syntax today? What is the term for this use? Can you use such non-aliases in other clauses (e.g. WHERE/HAVING)? If so, how does the engine know how to distinguish field #2 from the integer 2 (e.g. WHERE 2 is not NULL / HAVING 2 = 1)

[Tommix]

This SHOULD NOT be accepted answer cause it doesn't answer the question.What if in my SELECT i have IFF statement and then cast it? then order by column is not good, cause aliased column have different values and so on.

[Cazforshort]

Ah! From! I should have guessed. I was just trying to nest a Select inside a Select. Thanks