Re: small table, huge table, and a join = slow and tough query. cake inside!

edfialk wrote:
> SELECT small.fips, small.geom, small.name, SUM(huge.value) from small
> JOIN huge on huge.fips = small.fips WHERE (SUM(huge.value)) > 500 AND
> huge.pollutant='co';
>
> wonder if that makes sense.  Obviously, can't have an aggregate in
> where clause, so I've tried a couple WHERE (SELECT) kind of things,
> nothing working out too well.
>
> So first, if anyone has any idea on the best way I can do a WHERE
> (sum(huge.value) > 500)
> or...
> any ideas on how I could speed up the query, I would be so extremely
> grateful.

SELECT small.fips, small.geom, small.name, SUM(huge.value)
from small JOIN huge on huge.fips = small.fips
WHERE
huge.pollutant='co';
GROUP BY small.fips, small.geom, small.name
HAVING SUM(huge.value) > 500;

Regards,
Stephen Denne
At the Datamail Group we value teamwork, respect, achievement, client focus, and courage.
This email with any attachments is confidential and may be subject to legal privilege.
If it is not intended for you please advise by replying immediately, destroy it and do not
copy, disclose or use it in any way.

The Datamail Group, through our GoGreen programme, is committed to environmental sustainability.
Help us in our efforts by not printing this email.
__________________________________________________________________
  This email has been scanned by the DMZGlobal Business Quality
              Electronic Messaging Suite.
Please see http://www.dmzglobal.com/dmzmessaging.htm for details.
__________________________________________________________________