Thursday, February 6, 2014

T-SQL Error: Each GROUP BY expression must contain at least one column that is not an outer reference

I've written thousands (perhaps tens of thousands) of queries in at least half a dozen database engines. I consider myself pretty good at this query business. Unfortunately, I run into a quizzical error message every once in a while that makes me scratch my head.
Msg 164, Level 15, State 1, Line 163:Each GROUP BY expression must contain at least one column that is not an outer reference.

In general, I feel that Microsoft's error messages leave something to be desired. Oh, for instance, meaning, context, and substance.

Here's the story. I had a regular old query and I wanted to sum the financials for each period.

 @CURRENTYEAR as CurrentYear, 
 SUM( Z ) AS Z

No problem, right? Just add the non-aggregate columns into a GROUP BY statement and VOILA! But no. Didn't work after all. Why? First, let's parse the error message to see if we can actually get any meaning out of it.
  • expression
    • Any value made of constants or variables combined using operators
      • examples:
        • 10
        • @x
        • 10 + @x
  • must contain at least one column
    • Columns are by definition part of a table or table expression! Therefore any expression that doesn't have a column must be excluded. 
  • that is not an outer reference
    • This is a very abstract concept in terms of the SQL query language. I can't really do it justice here in one sentence, so I recommend reading up on something like Aggregates with an Outer Reference
Because there are variables that do not make reference to columns in some of the expressions, those expressions cannot be part of the GROUP BY clause. In other words, I had to remove the variable  @CURRENTYEAR from the GROUP BY list.

No comments:

Post a Comment