So, when your query specification has an explicit GROUP BY, your choices in what to use in post-grouping clauses (SELECT, HAVING, ORDER BY) are limited. You can only use aggregate functions, expressions in the GROUP BY list, or constants. An interesting possibility is that you can combine them. For example, the following is valid:

SELECT a+b FROM t GROUP BY a, b

Here, each of "a" and "b" are allowed in the SELECT list because they are present in the GROUP BY list, and so their combination as "a+b" is also allowed. However, the same combination rule becomes a bit less trivial when applied to expressions in the GROUP BY list (and we allow arbitrary expressions, remember?). For example, the following is allowed:

SELECT a+b FROM t GROUP BY a+b,

because the "a+b" in the SELECT list is an exact replica of the "a+b" in the GROUP BY  clause. However, if I try to apply the same "combination" technique I used for columns, it won't work:

SELECT a+b+c+d FROM t GROUP BY a+b, c+d

The reason this is not allowed is that a+b+c+d is parsed as ((a+b) + c) + d, and the fact that this is a combination of "a+b" and "c+d" won't be recognized. In fact, in all SQL Server versions up to SQL 2000, any combination of *expressions* (not simple variables) from the GROUP BY list is not recognized as valid, so that even if you write

SELECT (a+b) + (c+d) FROM t GROUP BY a+b, c+d,

or even simpler:

SELECT (a+b) + 1 FROM t GROUP BY a+b,

we won't recognize that as valid. This however changed in SQL 2005 Beta2, where both of the above examples will work. You should be careful however to compose your expressions so that the match is "obvious". For example, we are not smart enough to figure out that the following is valid:

SELECT a+1+b FROM t GROUP BY a+b,

but you can always structure your expressions so that the match is obvious (use parentheses if necessary).

This posting is provided "AS IS" with no warranties, and confers no rights.