[MonetDB-users] Aggregating by a computed field?

1 Jun 2011

      Hello,

I recently install MonetDB and have been trying to get a feel for its
speed and storage.  I ran into an issue with SELECT queries trying to
GROUP BY a computed field.

For example, imagine a table like this:

CREATE TABLE foo
(
  ts             TIMESTAMP NOT NULL,
  category   VARCHAR(32) NOT NULL
);

I want to count the number of categories by year, so I want to craft a
query like this:

sql> SELECT EXTRACT( year from ts ) as year, category, COUNT(category)
FROM foo GROUP BY 1, 2 ORDER BY 3 DESC;
syntax error, unexpected sqlINT in: "SELECT EXTRACT( year from ts ) as
year, category, COUNT(category) FROM foo GROUP BY 1, 2 ORDER BY 3
DESC"

Unfortunately, MonetDB does not appear to support positional fields in
the GROUP BY clause.  So I tried rewriting it like this:

sql> SELECT EXTRACT( year from ts ) as year, category, COUNT(category)
FROM foo GROUP BY EXTRACT( year from ts ), category ORDER BY 3 DESC;
syntax error, unexpected EXTRACT in: "SELECT EXTRACT( year from ts )
as year, category, COUNT(category) FROM foo GROUP BY EXTRACT( year
from ts ), category ORDER BY 3 DESC"

Ok, how about using "year" instead of the extract( .. ) bit?

sql> SELECT EXTRACT( year from ts ) as year, category, COUNT(category)
FROM foo GROUP BY year, category ORDER BY 3 DESC;
syntax error, unexpected YEAR in: "SELECT EXTRACT( year from ts ) as
year, category, COUNT(category) FROM foo GROUP BY year, category ORDER
BY 3 DESC"

What is the correct way to group by the computed field?  Should I
create a view?  Is there a positional syntax for the GROUP BY clause I
am overlooking?

$ monetdb --version
MonetDB Database Server Toolkit v0.8 (Apr2011)

$ monetdbd --version
MonetDB Database Server v1.4 (Apr2011)

$ mclient --version
mclient, the MonetDB interactive terminal (Apr2011)
support for command-line editing compiled-in

Thank you.

Synaptic Axon

Fabian Groffen

Synaptic Axon

Niels Nes

Niels Nes

Synaptic Axon

tags

participants (3)