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