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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You pass the expression you want to group by rather than the alias   SELECT       LastName          FirstName AS  FullName  FROM         customers GROUP BY      LastName          FirstName

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You pass the expression you want to group by rather than the alias   SELECT       LastName          FirstName AS  FullName  FROM         customers GROUP BY      LastName          FirstName

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You pass the expression you want to group by rather than the alias   SELECT       LastName          FirstName AS  FullName  FROM         customers GROUP BY      LastName          FirstName

User · Answer

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

User · Answer

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

User · Answer

You pass the expression you want to group by rather than the alias   SELECT       LastName          FirstName AS  FullName  FROM         customers GROUP BY      LastName          FirstName

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] How do I perform a GROUP BY on an aliased column in MS-SQL Server?

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to syntax