Re: SQL Count Magic Required.... First Iteration...

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