Each GROUP BY expression must contain at least one column that is not an outer reference

Today, a client DBA sent the following error to us while troubleshooting a very large piece of code that he had inherited:

Each GROUP BY expression must contain at least one column that is not an outer reference

The portion of the code that was running into this issue was using a constant in the GROUP BY clause and hence was running into this issue. Since it was a pretty long SQL statement, the developer had simply done a copy/paste from the Select columns list and put them in the group by clause. One of those values was a constant and hence they got this error. Here is a simple example to illustrate this:

SELECT
‘XYZ’ AS COL1
, [TYPE]
, COUNT(1) AS CNT
FROM SYS.OBJECTS
GROUP BY ‘XYZ’, [TYPE]

When you run this, you will receive the Msg 164 error. Simply remove the constant from the group by and you will be all set. You will also get the same error in case you are using a constant variable instead of a constant string.

Share this:

Like this:

LikeLoading...

Related

This entry was posted on December 14, 2009 at 11:42 pm and is filed under SQL Server.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.

2 Responses to “Each GROUP BY expression must contain at least one column that is not an outer reference”