On 01-06-2011 11:48:17 -0700, Synaptic Axon wrote:
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?
Niels knows exactly why this isn't allowed, but why do see this as an issue? I agree it clobbers the query, but that should be all...