From: | <operationsengineer1(at)yahoo(dot)com> |
---|---|
To: | Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: SQL Count Magic Required.... First Iteration... |
Date: | 2006-06-02 19:38:24 |
Message-ID: | 20060602193824.32955.qmail@web33303.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | tim barnard | 2006-06-02 20:52:08 | libpq-fe.h |
Previous Message | Tom Lane | 2006-06-02 19:19:16 | Re: Locale and Initdb Errors |