counts of groupings by date year-month

From: Carol Cheung <cacheung(at)consumercontact(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: counts of groupings by date year-month
Date: 2009-02-27 21:02:00
Message-ID: 49A854C8.2050505@consumercontact.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 | 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.

Is it possible to get counts by year_month?

Thanks for your help in advance,
CC

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Scott Marlowe 2009-02-27 21:47:17 Re: counts of groupings by date year-month
Previous Message Igor Neyman 2009-02-27 15:06:56 Re: row not deleted but updated (trigger?)