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

>
> --- 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
>

Thanks Adam, the following query runs perfectly.

<code>
SELECT Code, CAST(Stamp as DATE), COUNT(Stamp) FROM T1 GROUP BY Code, 2;
</code>

Thanks again,
Regards,
Ramazan Kartal