At 12:38 PM 7/12/2004 +0000, you wrote:

>Hello
>
>I have a table that contains a column CALL_START which is a Timestamp.
> I'd like to display a graph showing when calls arrived, grouped into
>user definable 'buckets'. i.e. the user can selct 5m, 10m, 15m, 20
>etc.
>
>Thus far I have:
>
>select CALL_START,
> CAST(CAST(CALL_START - CAST('12/04/2004' AS TIMESTAMP) AS double
>precision) / 0.01041666666666666 as Integer )
> from calls
> where CALL_START >= '12/04/2004' AND CALL_START <= '12/05/2004'
>
>
>NB: 0.01041666666666666 is a 15min period [ 15/(60 x 24) ]
>
>This lists the call start and also which 'bucket' the call belongs in,
>but I need to count how many calls are in each bucket and output the
>data like this:
>
>Bucket No, Bucket count
>1 5
>2 12
>3 45
>4 22
>etc
>
>I cannot seem to group by a 'CAST' column, e.g.
>
> select CAST(CAST(CALL_START - CAST('12/04/2004' AS TIMESTAMP) AS
>double precision) / 0.01041666666666666 as Integer ),
> count( CAST(CAST(CALL_START - CAST('12/04/2004' AS TIMESTAMP) AS
>double precision) / 0.01041666666666666 as Integer ) )
> from calls
> where CALL_START >= '12/04/2004' AND CALL_START <= '12/05/2004'
> group by CAST(CAST(CALL_START - CAST('12/04/2004' AS TIMESTAMP) AS
>double precision) / 0.01041666666666666 as Integer )
>
>This fails with token unknown 'CAST' on the group by line.
>
>Naming the first cast 'AS BucketNum' and then doing 'group by
>BucketNum' fails, with FB failing to recognise 'BucketNum'
>
>Any ideas?

What version of Firebird? If it's v.1.0.x, then your problem is that you
can't group by an expression unless it's a UDF expression.

If you *are* using v.1.5, then you could try the simpler GROUP BY 1. Under
the hood, the expression evaluation from the select specification will be
repeated, but it might eliminate the problem (without an obvious reason,
natch!)

Otherwise, wait for Arno to figure it out. :-))

./heLen