Thanks all.

Probably there is a version mismatch. I am using Derby 10.1 and this gives out an syntax error. I guess possibly it's already enhanced in newer versions.

On Tue, Jun 3, 2008 at 10:12 PM, Knut Anders Hatlen <Knut.Hatlen@sun.com> wrote:
"sin(EaTing)," <ussraf@gmail.com> writes:

> Hi,
>
> I am trying a statement like:
> SELECT count(*) FROM table1 GROUP BY YEAR(table1.the_time);
> I found it's OK in systems like DB2. But "syntax error" was given when I
> tried on Derby.

Hi,

I don't know why you get a syntax error. The query worked just fine for
me:

ij> create table table1 (x int, the_time timestamp);
0 rows inserted/updated/deleted
ij> insert into table1 values (1, current timestamp), (2, current timestamp);
2 rows inserted/updated/deleted
ij> insert into table1 values (3, current timestamp), (4, current timestamp), (5, current timestamp);
3 rows inserted/updated/deleted
ij> select count(*) from table1 group by the_time;
1
-----------
2
3

2 rows selected
ij> select count(*) from table1 group by year(table1.the_time);
1
-----------
5

1 row selected

--
Knut Anders