group by extract() error

I run this, to get a count of items by year, I get an error saying the
EXTRACT in line 3 is an invalid SQL name.

SELECT EXTRACT(YEAR FROM dt), count(*)
FROM "transactions"
GROUP BY EXTRACT(YEAR FROM dt)

"dt" is a Date column. Isn't this supposed to work?

Regards.

> SELECT EXTRACT(YEAR FROM dt), count(*)
> FROM "transactions"
> GROUP BY EXTRACT(YEAR FROM dt)

It will likely work if you add an alias to the select expression, and group
by the alias. Wether or not the above should work I leave to more SQL savvy
people.

--

Eivind Bakkestuen [NDD]
Please, no email unless requested.
Search Borland and third-party newsgroups here: www.tamaracka.com

On Thu, 1 Jul 2004 00:00:07 +1000, "Eivind Bakkestuen [NDD]"
<Ieivind.bakkestuenHATE@nexusdb.comSPAM> wrote:

>> SELECT EXTRACT(YEAR FROM dt), count(*)
>> FROM "transactions"
>> GROUP BY EXTRACT(YEAR FROM dt)
>
>It will likely work if you add an alias to the select expression, and group
>by the alias. Wether or not the above should work I leave to more SQL savvy
>people.

It does, thanks:

SELECT EXTRACT(YEAR FROM "dt") "Date", count(*)
FROM "transactions"
GROUP BY "Date"

However, the <ahem> manual implies that an alias is not needed (see
G.6.4.13 EXTRACT example). Please to tell the documentation scrivner
that the number of errors and omissions in the manual is now > 0.

Regards.

<< Wether or not the above should work I leave to more SQL savvy people.
>>

Standard SQL requires that grouping columns reference a column by its name,
i.e. the column alias if specified or the real column name if an alias is
not specified. NexusDB allows grouping columns to be specified by the column
name or alias interchangeably.

Ole Willy Tuv