From: | <operationsengineer1(at)yahoo(dot)com> |
---|---|
To: | operationsengineer1(at)yahoo(dot)com, 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.... Third Iteration... |
Date: | 2006-06-02 00:37:05 |
Message-ID: | 20060602003705.10749.qmail@web33312.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | gibboda | 2006-06-02 13:22:44 | Postgresql fail as boot time |
Previous Message | David | 2006-06-01 23:49:18 | Re: SQL Count Magic Required.... First Iteration... |