"Each GROUP BY expression must contain at least one column that is not an outer reference." reported when the GROUP BY contains a constant. Why?

Question

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';

Bergstein

what would be the use of grouping on a constant ? All values are the same so why you want to group on that ? – Amalburga

Let me guess - was the other system that allowed this mysql (or mariadb)? The design philosophies are somewhat different. SQL Server has "if the user has asked for something that doesn't make sense, report an error and have them fix it". MySQL has "if there's some way of reinterpreting what they've asked for so that I can produce a result set, any result set, let's do that and damn the torpedoes!". – Cleveite

@Amalburga I'm not saying I want to do it, it's just something that's come up during conversion from one database to another. I'm more interested as to why the error reported is incorrect. The third query does contain at least one column that is not an outer reference after all. – Bergstein

@Cleveite Informix, actually. I agree that it's fairly stupid code, but I wouldn't really call it an error. Grouping on a constant, so long as one of the other columns contains a non-OUTER reference, won't have any effect either way. Why not allow it and, more importantly, why produce an apparently incorrect error when it's done? – Bergstein

What makes you say the error is incorrect? Each GROUP BY expression must contain a non-outer column reference. In your third query, you have three GROUP BY expressions and the third contains absolutely no column references at all. – Cleveite

In sql-server each column in the group by must be an existing column from one of the referenced tables, and that is not the case in the 3th query. The error message is not always very clear that I agree with – Amalburga

@Cleveite I'm saying the specific error being reported is apparently incorrect. The error being reported is that the GROUP BY must contain at least one non-outer reference. It does. I'm asking why it reports that error when that error apparently does not apply. – Bergstein

No - look at the word I bolded previously, that also appears in the error message. The third GROUP BY expression in your third query is 'x'. That contains no column references. It's not referring to the entire GROUP BY clause but to each expression contained therein. – Cleveite

Answer 1

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.

Cleveite

Ah, right. My misunderstanding was coming from the fact that Informix allows for the use of indexed columns in the GROUP BY clause. Where these indexed columns contain constants, they still need to be grouped, and so need to appear in the GROUP BY clause. When converting to SQL Server, the columns can't be indexed and the constants lose their context, producing the logical error I wasn't seeing. That all makes sense now. Cheer. – Bergstein

Answer 2

The answer is just ignoring the constants; they don't have to be included in the group by clause because they are identical over all records:

declare @prevMonth as int set @prevMonth = MONTH(Getdate())-1

SELECT c.name, 2 as Report#, YEAR(Getdate()) as [Year], @prevMonth as MonthEnded ,c.agent_region,'agent aggregate' as ReportDesc 
, COUNT(c.cat_id) [Count cat]   
FROM agent_cat c
Group by c.name, c.agent_region

Note that all constant values are ignored in the group by clause. This is MS SQL.

Pyne