Trying to group on date parts

From: "Rob Anderson" <roba(at)bml(dot)uk(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Trying to group on date parts
Date: 2005-04-01 16:26:29
Message-ID: FHEPJNONMOLHMPDJDOCMEEDAHIAA.roba@bml.uk.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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!!!!

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Keith Worthington 2005-04-01 17:01:10 Calling trigger function from a function
Previous Message Tom Lane 2005-04-01 15:37:29 Re: VACUUM and other maintenance work