Re: SQL Count Magic Required.... First Iteration... - Mailing list pgsql-novice

thanks to help from Sean and Michael, this is the
working query...

SELECT 1.0 * sum(CASE WHEN inspect_result_pass THEN 1
ELSE 0 END) / count(total),
       sum(CASE WHEN inspect_result_pass THEN 1 ELSE 0
END),
       count(total)
FROM (SELECT DISTINCT ON (t_inspect.inspect_id)
t_inspect_result.inspect_result_pass
      FROM t_inspect_result, t_inspect,
t_inspect_area, t_serial_number,
           t_link_contract_number_job_number,
t_job_number, t_product
      WHERE t_inspect_result.inspect_id =
t_inspect.inspect_id
        AND t_inspect.serial_number_id =
t_serial_number.serial_number_id
        AND t_inspect_result.inspect_result_pass = 't'
        AND
t_serial_number.link_contract_number_job_number_id =

t_link_contract_number_job_number.link_contract_number_job_number_id
        AND
t_link_contract_number_job_number.job_number_id =
            t_job_number.job_number_id
        AND t_product.product_id =
t_job_number.product_id
        AND t_inspect.serial_number_id = 200
        AND t_product.product_number = 7214118000
        AND t_inspect_result.inspect_result_timestamp
> '2005-06-01'
        AND t_inspect_result.inspect_result_timestamp
< '2006-06-01'
      ORDER BY t_inspect.inspect_id,
inspect_result_timestamp ASC) AS total

of course, i use bind variable notation (?) for
serial_number_id and product_number.

thanks again.

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com