Before getting started I’d like to thank Simon Sabin, Paul White and Umachandar Jayachandran (UC) for their input in a discussion we held on the topic.
Related: Partial Aggregate
T-SQL doesn’t support a certain form of aggregate expressions that include outer references. As an example, consider the following query:
This query attempts to return, for each order, the average of all differences between the current order value and the values of all other orders by the same customer. However, when you try running this query, you get the following error:
The source for the restriction is in the SQL-92 standard, and SQL Server inherited it from the Sybase codebase. The problem is that SQL Server needs to figure out which query will compute the aggregate.
As you can see, the error message doesn’t say that outer references in aggregate expressions are banned altogether, but those that involve both outer and inner references are (even though the message text isn’t very clear about that). Here’s a simple example showing an aggregate expression with an outer reference that is allowed since you’re not mixing things:
Since there are only outer references in the first aggregate expression, it’s clear to SQL Server that the entire expression (AVG(O1.SubTotal)) should be resolved against the outer query, which is logically equivalent to:
Since there are only inner references in the second aggregate expression, It’s also clear to SQL Server that the entire expression (AVG(O2.SubTotal)) should be resolved against the inner query. But when you try mixing both outer and inner references, that’s when SQL Server gets confused and complains:
As mentioned, the message text is a bit misleading. It sounds like if you have an outer reference, only one outer column reference is allowed in the aggregate expression. But as the following supported example shows, that’s not really the case:
In short, the mixing of inner and outer references in aggregate expressions is what SQL Server seems to have a problem with.
Now let’s get back to the unsupported query I presented initially:
To remind you, this query attempts to return, for each order, the average of all differences between the current order value and the values of all other orders by the same customer. You want SQL Server to resolve the O2.SubTotal reference against the inner query and the O1.SubTotal reference against the outer query and apply the AVG function to the differences, but SQL Server won’t allow you to mix those. However, SQL Server will allow mixing those in expressions that are not aggregate expressions, e.g., predicates with correlations.
A simple workaround is to add another instance of the table as an inner instance and correlate that instance to the outer table by the table’s key. This way you can refer to elements from the outer row implicitly by referring to the respective elements from the new inner instance. Then the original inner instance can be joined to the new inner instance instead of being correlated to the outer one. But implicitly, it’s as if the original inner instance was correlated to the outer one. And then you can apply your aggregate expression mixing elements from the two inner instances—original and new. I bet this sounds confusing. Hopefully the code that implements this workaround will make things clearer:
This query is supported and addresses the original request.
You will face the same problem when using the APPLY operator, where the right table expression includes aggregate expressions that mix inner and outer references. Here’s an example for an unsupported query:
The workaround is the same:
If you have other workarounds of your own you are welcome to share those.