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

> Second Iteration continued...
>
> prior text deleted due to length...
>
> i decided to simplify the problem and only look at
> the
> select that is supposed count the first pass pass.
>
> the data is as follows...
>
> t_inspect
> inspect_id, sn_id
> 178, 200
> 179, 200
>
> t_inspect_result
> id, inspect_id, inspect_result_pass
> 27, 178, *false*
> 28, 179, *false*
> 31, 179, true
>
> ** designates first pass value, ie, not 2nd, 3rd,
> 4th,
> 5th, etc...  passes.
>
> so, the following query should yield 0, since zero
> first pass passes are logged.
>
> it returns one row with a value of 1 (as in number
> one).  if t_inspect_result_id 27 is changed to
> "true",
> it returns two rows, both with a value of 1.
>
> (SELECT DISTINCT ON (t_inspect_result.inspect_id)
> 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 t_inspect.inspect_id =
> t_inspect_result.inspect_id
>             AND inspect_result_pass = 't'
>             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'
>             GROUP BY t_inspect_result.inspect_id,
> t_inspect_result.inspect_result_timestamp

okay, this is working, albeit, it is a simplification
of the issue.

to get # initial passes:

SELECT COUNT (pass)
FROM (SELECT DISTINCT ON (t_inspect.inspect_id)
t_inspect_result.inspect_result_pass
      FROM t_inspect_result, t_inspect,
t_serial_number
      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.serial_number_id = 200
        AND t_inspect_result.inspect_result_pass = 't'
     ORDER BY t_inspect.inspect_id,
inspect_result_timestamp ASC)
      AS pass

to get # total:

SELECT COUNT (total)
FROM (SELECT DISTINCT ON (t_inspect.inspect_id)
t_inspect_result.inspect_result_pass
      FROM t_inspect_result, t_inspect,
t_serial_number
      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.serial_number_id = 200
     ORDER BY t_inspect.inspect_id,
inspect_result_timestamp ASC)
      AS total

both seem to be working as expected.  i found an old
thread that discussed this issue...

http://archives.postgresql.org/pgsql-sql/2004-04/msg00219.php

and i simplified the problem (complexity to be added
after structure is determined) - which i should have
done to begin with.

i think i can get the rest of the way tomorrow.  i'll
post the working query when i get it.

thanks for the help.

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