--- In firebird-support@yahoogroups.com, "ramazank58"
<ramazank58@...> wrote:

>
> Hi,
>
> Suppose I have a table created by using the following query.
>
> <code>
> DROP TABLE T1;
> COMMIT;
> CREATE TABLE T1 (Code CHAR(16) NOT NULL, Stamp TIMESTAMP NOT NULL,
> CONSTRAINT PK_T1 PRIMARY KEY (Code, Stamp));
> COMMIT;
> INSERT INTO T1 (Code, Stamp) VALUES ('A', '1/1/2006 8:00');
> INSERT INTO T1 (Code, Stamp) VALUES ('B', '1/1/2006 8:00');
> INSERT INTO T1 (Code, Stamp) VALUES ('B', '1/1/2006 9:00');
> INSERT INTO T1 (Code, Stamp) VALUES ('B', '1/2/2006 8:00');
> INSERT INTO T1 (Code, Stamp) VALUES ('C', '1/3/2006 8:00');
> COMMIT;
> </code>
>
> Now I need a query to group the records by code and date, something
> like this.
> <code>
> SELECT Code, CAST(Stamp AS DATE), COUNT(Stamp) FROM T1 GROUP BY

Code,

> CAST(Stamp AS DATE);
> </code>
>
> The returned query should look like this.
> <output>
> A 1/1/2006 1
> B 1/1/2006 2
> B 1/2/2006 1
> C 1/3/2006 1
> </output>
>
> I am in the process of changing my applications database access from
> in-house developed btree indexes to Firebird. Of course instead of
> using one 'Stamp' field with the data type as 'timestamp', I can use
> two fields with data types 'date' and 'time', but I was just

wondering

> if it is possible to group by and cast at the same time.
>
> Any ideas?

Yes, you must use the field number syntax rather than the field name
whenever an expression is involved. Fields are numbered 1 through N
starting on the left.

SELECT Code, CAST(Stamp AS DATE), COUNT(Stamp)
FROM T1
GROUP BY Code, 2;

Good luck.

Adam