counts of groupings by date year-month - Mailing list pgsql-sql

From Carol Cheung
Subject counts of groupings by date year-month
Date
Msg-id 49A854C8.2050505@consumercontact.com
Whole thread Raw
Responses Re: counts of groupings by date year-month
Re: counts of groupings by date year-month
Re: counts of groupings by date year-month
List pgsql-sql
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      |
172009-02-01  | t      |     17 2009-02-02  | f      |     21 2009-01-01  | t      |     20 2009-01-01  | t      |
212009-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.

Is it possible to get counts by year_month?

Thanks for your help in advance,
CC




pgsql-sql by date:

Previous
From: "Igor Neyman"
Date:
Subject: Re: row not deleted but updated (trigger?)
Next
From: Scott Marlowe
Date:
Subject: Re: counts of groupings by date year-month