GROUP BY can only be followed by a real column?

["sin(EaTing)," <us...@gmail.com>]

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.

So does it mean GROUP BY in Derby can only be followed by a real column
instead of some extra decoration?
And if not, how could I do something like to group by null and not null
like:
SELECT count(*) FROM table1 GROUP BY IS NULL(table1.the_time);

Thanks!

Re: GROUP BY can only be followed by a real column?

[Bryan Pendleton <bp...@amberpoint.com>]

sin(EaTing), wrote:
> 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.

Yes, definitely. Derby 10.2 added support for GROUP BY expressions:
https://issues.apache.org/jira/browse/DERBY-883

thanks,

bryan

P.S. 10.1 is fairly old now; Derby 10.4 has already been released!

Re: GROUP BY can only be followed by a real column?

["sin(EaTing)," <us...@gmail.com>]

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 <Kn...@sun.com>
wrote:

> "sin(EaTing)," <us...@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
>

Re: GROUP BY can only be followed by a real column?

[Knut Anders Hatlen <Kn...@Sun.COM>]

"sin(EaTing)," <us...@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

Re: GROUP BY can only be followed by a real column?

[Rick Hillegas <Ri...@Sun.COM>]

One way to solve this problem would be to group by the return value of a 
function which bins the time values into two buckets. Something like this:

import java.sql.*;
public class z
{
    public static int isNull( Time time )
    {
        if  ( time == null ) { return 1; }
        else { return 0; }
    }
}

The following script shows the sql needed:

create table timeTable
(
   timeValue time
);

create function isNull
(
    timeValue time
)
returns int
language java
parameter style java
no sql
external name 'z.isNull'
;

insert into timeTable
values ( null ), ( time('15:09:02') ), ( time('13:09:02') ), ( null ), ( 
null );

select t.isNull, count( t.isNull )
from
(
  select isNull( timeValue ) as isNull
  from timeTable
) t
group by t.isNull;

Hope this helps,
-Rick


sin(EaTing), wrote:
> 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.
>
> So does it mean GROUP BY in Derby can only be followed by a real 
> column instead of some extra decoration?
> And if not, how could I do something like to group by null and not 
> null like:
> SELECT count(*) FROM table1 GROUP BY IS NULL(table1.the_time);
>
> Thanks!