#0321 – SQL Server – Each GROUP BY expression must contain at least one column that is not an outer reference. – Msg 164


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.

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s