Re: counts of groupings by date year-month

Carol Cheung <cacheung(at)consumercontact(dot)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.
>
> Is it possible to get counts by year_month?

change count(year_month) to count(1), untested.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°