Msg 164, Level 15, State 1, Line 163:Each GROUP BY expression must contain at least one column that is not an outer reference.Gnarly.
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.
SELECT X, Y, @CURRENTYEAR as CurrentYear, SUM( Z ) AS Z FROM FINANCIALSUMMARY
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:
- 1
- 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.
No comments:
Post a Comment