Re: SQL Count Magic Required.... Third Iteration...
От | |
---|---|
Тема | Re: SQL Count Magic Required.... Third Iteration... |
Дата | |
Msg-id | 20060602003705.10749.qmail@web33312.mail.mud.yahoo.com обсуждение исходный текст |
Ответ на | Re: SQL Count Magic Required.... Second Iteration... (<operationsengineer1@yahoo.com>) |
Список | 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
В списке pgsql-novice по дате отправления: