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

[Gaizka Villate]

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    |     2
 table  | 01    |     1
 chair  | 02    |     1
 table  | 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
        where to_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
\.
;



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])