Sub-SELECT uses un-GROUPed attribute: how to solve.

Hi all!

I want to make a report of (let's say) object sales, and i'm getting the
error: Sub-SELECT uses un-GROUPed.

I think this will be better understand with an example:
(this is a over-simplified one, but it gets the point).


(See data model and data to fill it below.)

If i have:

SALES:
-------------------
Object | Datetime
-------------------
chair  | 2001-01-15
chair  | 2001-01-16
table  | 2001-01-17
chair  | 2001-02-15
table  | 2001-02-16


I want to get something as follows:

Object | Month | Sales | Total sales on that month
------------------------------------------

chair  | 01    | 2     |   3
table  | 01    | 1     |   3

chair  | 02    | 1     |   2
table  | 02    | 1     |   2


well, if i do:

select   object, to_char(sale_date, 'MM') as Month, count(*) as Sales   from sales   group by to_char(sale_date, 'MM'),
object;


object | month | sales
--------+-------+-------chair  | 01    |     2table  | 01    |     1chair  | 02    |     1table  | 02    |     1

It's Ok.

If i do:

select   object, to_char(sale_date, 'MM') as Month, count(*) as Sales,   (select count(*)       from sales s2
whereto_char(s2.sale_date, 'MM') = to_char(s1.sale_date, 'MM')   ) as Total_Sales   from sales s1   group by
to_char(sale_date,'MM'), object;
 


ERROR:  Sub-SELECT uses un-GROUPed attribute s1.sale_date from outer query


I get that error. I can understand it, since in the subselect i'm using
s1.sale_date that is not grouped yet.

My question is:
is there a way to get that result?

I've though of using a function, so it woud be:
select   object, to_char(sale_date, 'MM') as Month, count(*) as Sales,   total_for_month(sale_date, 'MM') as
Total_Sales  from sales s1   group by to_char(sale_date, 'MM'), object;
 


But i'd like to do it without using functions because i would have to
define a function for each different period i want to show results.
(for example, to get year or quarter totals instead of months).

I think this is a common query to report sales. Does anybody found a
beautiful solution?


Thanks for your attention.

-- Gaizka Villate



-- Data model for the example:


create table sales (   object  varchar(30),   sale_date date
);

copy sales from stdin using delimiters '|';
chair|2001-01-15
chair|2001-01-16
table|2001-01-17
chair|2001-02-15
table|2001-02-16
\.
;