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_LEVEL ), COUNT(1)
FROM package_rules_expanded PRE, ACCOMMODATION_AVAILABILITY AA
WHERE AA.CODE = ACCOMM_CODE AND AA.CODE_TYPE = ACCOMM_CODE_TYPE
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".accomm_code = "inner".code) AND ("outer".room_type = "inner".room_type) AND ("outer".accomm_code_type = "inner".code_type))
Join Filter: (("inner".date >= "outer".outward_date)AND ("inner".date <= ("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_expanded pre (cost=0.00..28271.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_availability aa (cost=0.00..77409.40 rows=3942640 width=40)