Systems Engineering and RDBMS

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

Posted by decipherinfosys on December 14, 2009

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.

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

  1. cloud computing services,cloud computing hosting,clouding computing,cloud computing service,cloud computing platform,cloud computing server,cloud computing security,cloud computing solution,cloud computing technologies,cloud based solution,cloud serv…

    […]Each GROUP BY expression must contain at least one column that is not an outer reference « Systems Engineering and RDBMS[…]…

  2. Saranya S said

    very good and simple explanation.

Leave a Reply

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

 
%d bloggers like this: