Thread: counts of groupings by date year-month

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

On Fri, Feb 27, 2009 at 2:02 PM, Carol Cheung
<cacheung@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);

Carol Cheung <cacheung@consumercontact.com> writes:
> 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;

The only problem in what you wrote was the illegal cross-reference
from one output column to another.  Just use count(*) instead.
        regards, tom lane

Carol Cheung <cacheung@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.
>
> 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°