summary aggregate information from a second table

[Matthew Lunnon]

Hi,

I have two tables and I want to get summary information from the second table for each row of the first table, I can see two ways to do this one is with the SQL below but since the first table is very big the group by takes along time and there is no need since it is unique. The second way is witha function which loops through each row in the first table and does the aggregate function for that row. Does anyone know of a way to do this with SQL or will I have to use a function?

Thanks in advance
Matthew
EXPLAIN select ID, MIN( AA.ALLOCATION - AA.BOOKING_LEVE L ), COUNT(1)
FROM package_rules_e xpanded PRE, ACCOMMODATION_A VAILABILITY AA
WHERE AA.CODE = ACCOMM_CODE AND AA.CODE_TYPE = ACCOMM_CODE_TYP E
AND AA.ROOM_TYPE = PRE.ROOM_TYPE
AND AA.DATE BETWEEN OUTWARD_DATE AND ( RETURN_DATE - 1 )
GROUP BY ID;

Aggregate (cost=23229579. 28..23641565.44 rows=4119862 width=78)
-> Group (cost=23229579. 28..23435572.36 rows=41198616 width=78)
-> Sort (cost=23229579. 28..23332575.82 rows=41198616 width=78)
Sort Key: pre.id
-> Merge Join (cost=893507.72 ..10179309.28 rows=41198616 width=78)
Merge Cond: (("outer".accom m_code = "inner".cod e) AND ("outer".room_t ype = "inner".room_ty pe) AND ("outer".accomm _code_type = "inner".code_ty pe))
Join Filter: (("inner".dat e >= "outer".outward _date)AND ("inner".dat e <= ("outer".return _date - 1)))
-> Sort (cost=80147.71. .81166.21 rows=407400 width=38)
Sort Key: pre.accomm_code , pre.room_type, pre.accomm_code _type
-> Seq Scan on package_rules_e xpanded pre (cost=0.00..282 71.00 rows=407400 width=38)
-> Sort (cost=813360.01 ..823216.61 rows=3942640 width=40)
Sort Key: aa.code, aa.room_type, aa.code_type
-> Seq Scan on accommodation_a vailability aa (cost=0.00..774 09.40 rows=3942640 width=40)