Re: GROUPing problem

I think this should work:

select date_part('month', signedup) as month,date_part('year', signedup)
as year, count(*) from member group by year,month order by year,month

Hope, it helps...

Dima

Kurt Overberg wrote:
> Hi all, I'm hoping someone can shed some light on something for me.
> This will most likely be one of those newbie questions, but I'm having
> a real hard time understanding this, and would appreciate some help.
>
> I'm trying to build a query to return number of records per month.  My
> query looks like:
>
>  select date_part('month', signedup), count(*) from member group by
> date_part;
>
> ...this works great, it returns:
>
>  date_part | count
> -----------+-------
>          1 |   842
>          2 |   205
>          9 |   863
>         10 |   770
>         11 |   687
>         12 |   832
>
> ...however, the data is from september to february, so I'd like to
> sort by year, to get the months in the proper order - so when I try:
>
> date_part('month', signedup) as month, count(*) from member group by
> date_part order by date_part('year', signedup) asc;
>
> I get:
>
> ERROR:  Attribute member.signedup must be GROUPed or used in an
> aggregate function
>
> I don't understand why just adding the 'order by' makes
> member.signedup suddenly need to be included in the group by section.
> I want the same results, I just want them ordered differently.  Plus,
> if I do include member.signedup, it ruins my aggregation.
>
> I'm using postgresql 7.2 on debian.
>
> Any thoughts would be appreciated.  Thanks!
>
> /kurt