Re: [GENERAL] hopefully a brain teaser, can't quite figure out query

> -----Original Message-----
> The small table is a listing of county fips codes, their name, and the
> geometry for the county. Each fips is only listed once. The big table
> is multiple emissions for each county, the parameter for the emission,
> and the source code for the emission (scc). Each county in big tbale
> has many entries, variable number of pollutant types, variable number
> of scc's.
>
>
> SELECT small.fips, small.name, sum(big.value)
> FROM small, big
> WHERE
> small.fips in (
> SELECT fips from big
> WHERE ((pollutant='co') AND
> (
> (scc LIKE '21%') OR (scc LIKE '2301%') OR (scc LIKE '2801000000%')
> )
> HAVING SUM(value > 2000)
> )
> GROUP BY small.fips, small.name;
>
> This is the query that isn't returning yet.
> If anyone has any questions, comments, or any suggestions at all, I'll
> do my best to respond ASAP.

This sounds like what you want:

SELECT small.fips, small.name, sum(big.value) as big_sum
FROM small
INNER JOIN big on small.fips = big.fips
WHERE pollutant = 'co'
AND (scc LIKE '21%' OR scc LIKE '2301%' OR scc LIKE '2801000000%')
GROUP BY small.fips, small.name

However, I'm not sure I understand this part:

> I would return the 123 fips ONLY if the value provided was less
> than the sum of the values for all scc's (500+550+1500+50 = 2600),
> as well as the sum for those values.

Can you clarify?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general