[SQL] sub-select with aggregate

[Tomasz Myrta]

Hi
I want to perform query looking like this:

select
user_id,
a/sum_a as percent_a,
b/sum_b as percent_b
from
users join
(select
group_id,
sum(a) as sum_a,
sum(b) as sum_b
from users group by group_id) X using (group_id)
where group_id=3;

This query works, but very slow. Subquery with aggregate is performed for all table rows instead of group_id=3.

I wish I could write it like this, but it doesn't work at all:
select
user_id,
a/sum_a as percent_a,
b/sum_b as percent_b
from
users U,
(select
sum(a) as sum_a,
sum(b) as sum_b
from users where group_id=U.group_id) X
where group_id=3;

I don't want to make it as single query with "group by", because it has a lot of fields and "group by" would be very long.

Regards,
Tomasz Myrta


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]