Thread: Trying to group on date parts

I am trying to select items from a file and to group on the CCYY-MM part of
the date, in order to give a total count of items for that period

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;

However I get the error message

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

I have also used date_trunc('month', mylog.datetime) with the same error

Help!!!!

"Rob Anderson" <roba@bml.uk.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