Re: SQL Count Magic Required....

On 5/31/06 7:32 PM, "operationsengineer1(at)yahoo(dot)com"
<operationsengineer1(at)yahoo(dot)com> wrote:

> i have the following query that yields a series of
> true or false results:
>
> -- distinct on is pgsql extension --
> SELECT DISTINCT ON (t_inspect_result.inspect_id)
> t_inspect_result.inspect_result_id,
>
> t_inspect_result.inspect_result_pass,
> t_inspect_area.inspect_area,
>
> t_inspect_result.inspect_result_timestamp
> --,t_inspect.serial_number_id,
> t_inspect.inspect_id
> 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.inspect_area_id =
> t_inspect_area.inspect_area_id
> AND t_inspect.serial_number_id =
> t_serial_number.serial_number_id
> 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.inspect_id =
> t_inspect_result.inspect_id
> AND t_inspect.serial_number_id = '200'
> ORDER BY t_inspect_result.inspect_id DESC,
> t_inspect_result.inspect_result_timestamp ASC
> -- used to get first pass yield pass / fail (true /
> false) data.
> -- inspect_id desc impacts end result. time desc
> impacts the groups prior to being distinctly listed
>
> the simplified output may look like
>
> f,t,t,f,f,f,t,f,t,f
>
> the COUNT magic comes into play b/c i want to count
> the result set's "t"s and total, but i have no clue
> how to get this done.
>
> #ts: 4
> #total: 10
>
> when i have this data, i can apply some math and come
> up with a 40% yield.

You can do a query like (untested, and needs to be translated into your
monster query):

select a.id,a.total,b.failed,(a.total::numeric)/b.total as yield
from (select count(test_result) as total from table) as a,
(select count(test_result) as failed from table where
test_result='f') as b where a.id = b.id;

The point is to do the queries separately as subqueries and join them on
some primary key so that you get the count "total" and the count "failed".
Then you can do the math as above. Note that you have to cast at least one
of the integers to numeric if you want a numeric result.

Sean