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

[Rick Hillegas]

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!