Hi,

> I have a dialect 3 database (FB 1) with a table that has a TIMESTAMP
> column (fecha). I executed the following query:
>
> SELECT CAST(fecha AS DATE) AS fecha
> FROM telefono
> GROUP BY fecha;
>
> I would expect a result set of unique dates, but instead I recieve
> repeated rows. How come?

You are grouping by the original column (fecha), not by the alias. The
GROUP BY of Firebird 1 can only group on existing columns.

> I would like to build an histogram of the same table. The idea is to
> compute the number of rows in the table grouped by date and hour of
> the day. Is there any way I could do something like:
> (I have no idea how to decompose a timestamp/date field into year,
> month, day, etc.)
>
> SELECT CAST(fecha AS YEAR) AS ano,
> CAST(fecha AS MONTH) AS mes,
> CAST(fecha AS DAY) AS dia,
> CAST(fecha AS HOUR) AS hora,
> AVG(duracion)
> FROM telefono
> GROUP BY CAST(fecha AS YEAR) AS ano,
> CAST(fecha AS MONTH) AS mes,
> CAST(fecha AS DAY) AS dia,
> CAST(fecha AS HOUR) AS hora;
>

With Firebird 1.5 you will be able to use:

GROUP BY 1, 2, 3, 4




With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase
http://www.upscene.com

Firebird Workbench - the developer tool for Firebird
http://www.upscene.com

Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."