Обсуждение: Re: SQL Count Magic Required.... Second Iteration...

Поиск
Список
Период
Сортировка

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

От
Дата:
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

as always, any help or guidance is appreciated.

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

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

От
Дата:
> 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