|
|
Error Message
Server: Msg 164, Level 15, State 1, Line 1
Each GROUP BY expression must contain at least one
column that is not an outer reference.
Causes
This error message is a little bit vague or a bit confusing especially when the SELECT statement generating this error does not have any JOIN clauses and therefore is not performing any “outer reference”. In SQL Server 2000, this same error code has a different error message associated to it:
|
Msg 164, Level 15, State 1, Line 1
GROUP BY expressions must refer to column names that appear in the select list.
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
Each GROUP BY expression must contain at least one column that is not an outer reference.
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
Each GROUP BY expression must contain at least one column that is not an outer reference.
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.
|