|
|
Error Message
Server: Msg 164, Level 15, State 1, Line 1
GROUP BY expressions must refer to column names
that appear in the select list.
Causes
This error message is a little bit vague or a bit confusing because this error occurs on a SELECT statement with the GROUP BY clause and the list in the GROUP BY clause all appear in the SELECT clause list, including any literal constant values or local variables. In SQL Server 2005 or SQL Server 2008, this same error code has a different error message associated to it:
|
Msg 164, Level 15, State 1, Line 1
Each GROUP BY expression must contain at least one column that is not an outer reference.
This error can easily be reproduced by the including a constant value in the SELECT clause and in the GROUP BY clause of a SELECT statement, as shown in the following script:
CREATE TABLE [dbo].[Sales] (
[SalesPerson] VARCHAR(50),
[SalesDate] DATETIME,
[Amount] MONEY
)
SELECT [SalesPerson], 2011 AS [SalesYear], SUM([Amount]) AS [TotalSales], COUNT(*) AS [SalesCount]
FROM [dbo].[Sales]
WHERE [SalesDate] >= '01/01/2011' AND
[SalesDate] < '01/01/2012'
GROUP BY [SalesPerson], 2011
Msg 164, Level 15, State 1, Line 5
GROUP BY expressions must refer to column names that appear in the select list.
This SELECT statement returns the total sales amount and total sales count of each sales person for the year 2011. As part of the output, the year is included in the list and therefore is being included in the GROUP BY clause.
Another way of getting this error is with the use local variables in the SELECT clause and GROUP by clause, as shown in the following SELECT statement:
DECLARE @Year INT
SET @Year = 2011
SELECT [SalesPerson], @Year AS [SalesYear], SUM([Amount]) AS [TotalSales], COUNT(*) AS [SalesCount]
FROM [dbo].[Sales]
WHERE YEAR([SalesDate]) = @Year
GROUP BY [SalesPerson], @Year
Msg 164, Level 15, State 1, Line 5
GROUP BY expressions must refer to column names that appear in the select list.
This SELECT statement also returns the same output as the one previous to it, which is the total sales amount and total sales count for each sales person for the year 2011, grouping the output by sales person and year.
Solution / Work Around:
To avoid this error, when using literal constants or local variables in a SELECT statement with the GROUP BY clause, just don’t include the literal constant or local variable in the GROUP BY clause but leave it in the SELECT clause.
Using the first example, the following SELECT statement will return the desired output without generating the error:
SELECT [SalesPerson], 2011 AS [SalesYear], SUM([Amount]) AS [TotalSales], COUNT(*) AS [SalesCount]
FROM [dbo].[Sales]
WHERE [SalesDate] >= '01/01/2011' AND
[SalesDate] < '01/01/2012'
GROUP BY [SalesPerson]
The only difference between this SELECT statement and the one that is generating the error is in the GROUP BY clause where the literal constant 2011 representing the sales year is removed.
Similarly for the second example, the following SELECT statement will return the desired output without generating the error.
DECLARE @Year INT
SET @Year = 2011
SELECT [SalesPerson], @Year AS [SalesYear], SUM([Amount]) AS [TotalSales], COUNT(*) AS [SalesCount]
FROM [dbo].[Sales]
WHERE YEAR([SalesDate]) = @Year
GROUP BY [SalesPerson]
The only difference between this SELECT statement and the one that is generating the error is in the GROUP BY clause where the local variable @Year representing the sales year is not included.
|