I was attempting to write an ad-hoc query which aggregated some data using the GROUP BY clause for a quick data check when a copy-paste error made me stumble upon the following error:

Msg 164, Level 15, State 1, Line 8
Each GROUP BY expression must contain at least one column that is not an outer reference.

The query that returned the error looked like:

USE AdventureWorks2008R2 ;
GO
DECLARE @accountNumber VARCHAR(30) = '10-4020-000676'

SELECT  YEAR(OrderDate) AS PurchaseYear,
        @accountNumber AS AccountNumber,
        SUM(TotalDue) AS TotalPurchase
FROM    Sales.SalesOrderHeader
WHERE   AccountNumber = @accountNumber
GROUP BY YEAR(OrderDate),
        @accountNumber ;
GO

What I typically do when I work with the group by clause is that I take the columns from the SELECT clause, remove the aggregations and use that list for the GROUP BY clause. In that process, I ended up with a variable (@accountNumber) in the GROUP BY list. A variable is ultimately an expression which was treated by SQL Server as an outer reference – which is not allowed in T-SQL. The solution therefore is to change the list for the GROUP BY to not use variables.

USE AdventureWorks2008R2 ;
GO
DECLARE @accountNumber VARCHAR(30) = '10-4020-000676';

SELECT  YEAR(OrderDate) AS PurchaseYear,
        @accountNumber AS AccountNumber,
        SUM(TotalDue) AS TotalPurchase
FROM    Sales.SalesOrderHeader
WHERE   AccountNumber = @accountNumber
GROUP BY YEAR(OrderDate),
        AccountNumber ;  --Notice that the variable has been replaced 
                         --with the corresponding field name
GO

[Edit – 02/16/2014 – 01:20AM IST]:

Based on a couple of comments that I received on this post, I would like to elaborate further on the behaviour that is exhibited by SQL Server.

• The GROUP BY clause can operate on any expressions except single-row, single-column sub-queries


• This expression must contain at least one column belonging to the tables referenced in the FROM clause of the statement where GROUP BY is being applied


• A variable is a single-row, single-column expression (SELECT @accountNumber is perfectly valid), and does not reference any column from the tables used in the statement – this is what makes the variable invalid in the GROUP BY clause

Until we meet next time,

Be courteous. Drive responsibly.