On Fri, Feb 27, 2009 at 2:02 PM, Carol Cheung <cache...@consumercontact.com> wrote: > Hi, > I have a table called temp > > access_date | active | status > -------------+--------+-------- > 2009-02-01 | t | 15 > 2009-02-01 | f | 16 > 2009-02-02 | f | 17 > 2009-02-01 | t | 17 > 2009-02-02 | f | 21 > 2009-01-01 | t | 20 > 2009-01-01 | t | 21 > 2009-01-01 | f | 21 > > > What I want is to be able to get counts of active by year-month. So the > output would be like: > > year_month | count > ------------+------- > 200901 | 3 > 200902 | 5 > > I tried something like > SELECT to_char(access_date, 'YYYYMM') as year_month, count(year_month) FROM > temp GROUP BY year_month ORDER BY year_month; > > but I'm unable to execute this query because the column "year_month" doesn't > exist in temp table.
Try date_trunc: select date_trunc('day',timestamp), count(*) from table where active is true group by date_trunc('day',timestamp) order by date_trunc('day',timestamp); -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql