I do not know definite answer as to why below query would fail, however i read it somewhere 'Column Alias cannot be used in group by clause'.
"time" is column which contains string value in the format [15/Jul/2009:14:58:59 - 0700], to extract "year" field write series of UDF's and used as below.
select year(from_unixtime(unix_timestamp(t1.time, '[dd/MMM/yyyy:HH:mm:ss Z]'))) as `year` , COUNT(*) from acceslogs as t1 group by `year`;
Need a confirmation why above query would fail. Thanks in advance.