Why can't I GROUP BY 1 when it's OK to ORDER BY 1?

Question

Why are column ordinals legal for ORDER BY but not for GROUP BY? That is, can anyone tell me why this query

SELECT OrgUnitID, COUNT(*) FROM Employee AS e GROUP BY OrgUnitID

cannot be written as

SELECT OrgUnitID, COUNT(*) FROM Employee AS e GROUP BY 1

When it's perfectly legal to write a query like

SELECT OrgUnitID FROM Employee AS e ORDER BY 1

?

I'm really wondering if there's something subtle about the relational calculus, or something, that would prevent the grouping from working right.

The thing is, my example is pretty trivial. It's common that the column that I want to group by is actually a calculation, and having to repeat the exact same calculation in the GROUP BY is (a) annoying and (b) makes errors during maintenance much more likely. Here's a simple example:

SELECT DATEPART(YEAR,LastSeenOn), COUNT(*)
    FROM Employee AS e
    GROUP BY DATEPART(YEAR,LastSeenOn)

I would think that SQL's rule of normalize to only represent data once in the database ought to extend to code as well. I'd want to only right that calculation expression once (in the SELECT column list), and be able to refer to it by ordinal in the GROUP BY.

Clarification: I'm specifically working on SQL Server 2008, but I wonder about an overall answer nonetheless.

Answer 1

use aliasses :

SELECT DATEPART(YEAR,LastSeenOn) as 'seen_year', COUNT(*) as 'count'
    FROM Employee AS e
    GROUP BY 'seen_year'

** EDIT **

if GROUP BY alias is not allowed for you, here's a solution / workaround:

SELECT seen_year
     , COUNT(*) AS Total 
  FROM (
    SELECT DATEPART(YEAR,LastSeenOn) as seen_year, *
    FROM Employee AS e
  ) AS inline_view
GROUP 
    BY seen_year

Answer 2

One of the reasons is because ORDER BY is the last thing that runs in a SQL Query, here is the order of operations

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

so once you have the columns from the SELECT clause you can use ordinal positioning

EDIT, added this based on the comment Take this for example

create table test (a int, b int)
insert test values(1,2)
go

The query below will parse without a problem, it won't run

select a as b, b as a
     from test
    order by 6

here is the error

Msg 108, Level 16, State 1, Line 3
The ORDER BY position number 6 is out of range of the number of items in the select list.

This also parses fine

select a as b, b as a
     from test
    group by 1

But it blows up with this error

Msg 164, Level 15, State 1, Line 3
Each GROUP BY expression must contain at least one column that is not an outer reference.

Answer 3

I'm not sure if the standard specifies if it is valid, but I believe it is implementation-dependent. I just tried your first example with one SQL engine, and it worked fine.