Re: Re: [SQL] Difficult SQL Statement - Mailing list pgsql-general

"Tim Barnard" <tbarnard@povn.com> writes:
> To my thinking there's got to be a better way to do this whithout so many
> temporary tables.

In 7.1 you can frequently replace temp tables with subselect-in-FROM.
Cutting-and-pasting freely from your solution:

select ASMT_CODE, PASSED, TOTAL, PASSED::float4/TOTAL::float4
from
    (select ASMT_CODE, count(*) as TOTAL from RESULTS
     group by ASMT_CODE) as tmp1
  natural join
    (select ASMT_CODE, count(*) as PASSED from RESULTS
     where STATUS='PASSED' group by ASMT_CODE) as tmp2

(haven't tried this, but it looks right...)

This won't necessarily be a whole lot faster than the solution with
temp tables, but it's nice not to have to worry about dropping the
temp tables afterwards.

            regards, tom lane