How to Correctly GROUP BY Aliased Columns (Including CASE Statements) in SQL Server?

Preserving GROUP BY operation of alias columns in SQL Server

In SQL Server, you may encounter challenges when performing GROUP BY operations using alias columns. This article explores the correct syntax for performing GROUP BY operations on aliased columns and checks whether this syntax also works for CASE statement aliased columns.

To perform a GROUP BY operation on an aliased column, the expression used to create the alias must be quoted in the GROUP BY clause. For example:

<code class="language-sql">SELECT LastName + ', ' + FirstName AS 'FullName'
FROM customers
GROUP BY LastName + ', ' + FirstName</code>

In this scenario, the alias "FullName" is created using the expression "LastName ', ' FirstName". To group by this alias, the complete expression must be specified: 'LastName ', ' FirstName'.

Also, the same syntax can be applied to CASE statement alias columns:

<code class="language-sql">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</code>

In this case, the CASE statement is used to determine the value of the "FullName" alias based on the values ​​of the "LastName" and "FirstName" columns. To group by this alias column, reference the expressions 'LastName ', ' FirstName' and 'FirstName'. Therefore, the GROUP BY clause can contain:

<code class="language-sql">GROUP BY CASE
        WHEN LastName IS NULL THEN FirstName
        WHEN LastName IS NOT NULL THEN LastName + ', ' + FirstName
    END</code>

However, it is important to note that when using the CASE statement to alias columns in a GROUP BY operation, all referenced column expressions must appear in the GROUP BY clause. This ensures that the aggregation results are valid and accurate.

The above is the detailed content of How to Correctly GROUP BY Aliased Columns (Including CASE Statements) in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!