There are a multitude of questions relating to the "Each GROUP BY expression must contain at least one column that is not an outer reference." error, and the inclusion of a constant value in the GROUP BY clause is often the cause of the error.
I'm actually converting some SQL from a database that allows constants in the GROUP BY clause, so I'm wondering two things:
Why does MS SQL Server not allow constants in the GROUP BY clause?
Why does putting a constant in the GROUP BY clause produce this error and not a "Don't put constants in your GROUP BY clause, idiot." error?
Here's a quick example of the error occurring. The following code bombs out on the third SELECT statement, with the incongruous error message.
DECLARE @demo TABLE
( groupCol1 int
, groupCol2 int);
INSERT INTO @demo VALUES(1,1);
INSERT INTO @demo VALUES(1,1);
INSERT INTO @demo VALUES(2,1);
INSERT INTO @demo VALUES(1,2);
INSERT INTO @demo VALUES(1,2);
INSERT INTO @demo VALUES(2,2);
INSERT INTO @demo VALUES(3,1);
INSERT INTO @demo VALUES(3,1);
INSERT INTO @demo VALUES(1,3);
INSERT INTO @demo VALUES(1,3);
SELECT * FROM @demo;
SELECT * FROM @demo GROUP BY groupCol1, groupCol2;
SELECT *, 'x' FROM @demo GROUP BY groupCol1, groupCol2, 'x';
The error is produced because the query contains a logical error. You have a GROUP BY expression that is equal for all rows. It therefore doesn't meaningfully divide the result set into groups. Some database systems are more tolerant of logical errors and will try to produce a result set. SQL Server isn't very tolerant of such errors. It wants you to really think hard and actually tell it what you want it to do.
You can include values that are constant in many places in T-SQL - but not in places where they do not make a logical difference.