Good day and welcome back to SQL Something!
I'm ashamed at how long I've been away and there is really no excuse. It is, however, a new year (2015 people!), so I'm going to try to be much better. Here's hoping!
We'll start this year with a very simple error that you may see when running a query:
"Each GROUP BY expression must contain at least one column that is not an outer reference"
The error itself is a bit vague, but refers to something quite simple. As the above error indicates, a "Group By" is involved. Maybe your query involves a variable like so:
SELECT COUNT([Column1]) AS TotalCount, [Column2], @Variable as VariableName
FROM [Schema1].[Table1]
WHERE [Column2] = @Variable
GROUP BY [Column2], @Variable;
Do you know what's wrong? It's the variable in the GROUP BY clause.
Normally, when using GROUP BY, you are required to put all the columns in the SELECT clause that are not part of a aggregate function into the GROUP BY clause. Following that logic you should also put any local variables that you are using in the SELECT clause. This however is not so.
Removing the variable like so fixes the query:
SELECT COUNT([Column1]) AS TotalCount, [Column2], @Variable as VariableName
FROM [Schema1].[Table1]
WHERE [Column2] = @Variable
GROUP BY [Column2];
Thank you, as usual, for your time. :-)
DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to
politely correct or comment as you see fit. Your feedback is always
welcomed. :-)
