On 27 Oct 2005 at 9:12, steffengrondahl wrote:

>select contactno from contacts c
>where
>state in (1,2)
>and
>(select sum(giftvalue) from gifts g where g.contactno = c.contactno
>and someotherfield = 'somvalue') >= 100
>
>This takes several hours (contacts haves about 55,000 record and gifts
>about 1,000,000). If I make a simple query and let java do the work I
>can obtain the desired result in a few seconds:

Try this:

select c.contactno from contacts c
join gifts g where g.contactno = c.contactno
where
c.state in (1,2)
and g.someotherfield = 'somvalue'
group by contactno
having sum(giftvalue)>100

--
SY, Dimitry Sibiryakov.