Re: Re: [SQL] Difficult SQL Statement

"Tim Barnard" <tbarnard(at)povn(dot)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