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

> > On 5/31/06 7:32 PM,
> "operationsengineer1@yahoo.com"
> > <operationsengineer1@yahoo.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.
>
> for those following on (probably just yours truly
> ;-),
> the first iteration (tested and works):
>
> SELECT a.total, b.passed,
> b.passed/(a.total::numeric)
> as yield
> FROM (SELECT count(inspect_result_pass) as total
>       FROM t_inspect_result)
>       AS a,
>      (SELECT count(inspect_result_pass) as passed
>       FROM t_inspect_result
>       WHERE inspect_result_pass = 't')
>       AS b
>
> this generates results based on the whole table (not
> limited to first entry and not limited by unique
> product/serial combo.
>
> in my test case, i have 5 passes and 9 total and 5/9
> is displayed as
>
> 0.55555555555555...

the second iteration is working, too.  it counts the
passes and total by serial_number_id (i will
eventually use the product id and the serial number to
determine the serial_number_id, but i've excluded that
"noise" to keep this simpler).

again, it works on the whole table, not just those
passes and fails that were the first inspection for a
given inspection (remember, this is first pass yield,
not second, third or fourth pass yield).

eg,

inspect #1: *pass*
inspect #2: *fail*, fail, fail, fail, fail, pass
inspect #3: *fail*, pass

first pass yield = 1 / 3 = 33.3%.

in my previous query, i resolved this by doing:

SELECT DISTINCT ON (t_inspect_result.inspect_id)...
...
ORDER BY t_inspect_result.inspect_id DESC,
t_inspect_result.inspect_result_timestamp ASC

i have no clue how to implement something similar
given the different SQL structure below...

SELECT a.passed, b.total, a.passed/(b.total::numeric)
as yield
FROM (SELECT count(inspect_result_pass) as passed
      FROM t_inspect_result, t_inspect,
t_inspect_area,
           t_serial_number,
t_link_contract_number_job_number, t_job_number,
t_product
      WHERE inspect_result_pass = 't'
            AND t_inspect.inspect_id =
t_inspect_result.inspect_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_area_id =
                t_inspect_area.inspect_area_id
            AND t_inspect.serial_number_id = '200'
      )
      AS a,
     (SELECT count(inspect_result_pass) as total
      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_id =
t_inspect_result.inspect_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_area_id =
                t_inspect_area.inspect_area_id
            AND t_inspect.serial_number_id = '200'
      )
      AS b


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