Re: Trying to group on date parts

"Rob Anderson" <roba(at)bml(dot)uk(dot)com> writes:
> SELECT
> SUBSTRING(mylog.datetime,1,7) AS datetime,
> count(*) as counter
> FROM mylog
> WHERE mylog.datetime<'2005-02-02'
> GROUP BY SUBSTRING(mylog.datetime,1,7)
> ORDER BY mylog.datetime;

> ERROR: column "mylog.datetime" must appear in the GROUP BY clause or be
> used in an aggregate function

Try
ORDER BY SUBSTRING(mylog.datetime,1,7);

Your original isn't legal because there's not a unique value of
mylog.datetime for each group. You know and I know that that doesn't
really matter in this case, but the software is just mechanically
enforcing the SQL rule that says the SELECT and ORDER BY items have
to have unique values in each group.

regards, tom lane