hi all, i posted this problem on the novice thread, but it makes much more sense to post it here, instead. sorry fo rthe double posting, i'll be sure to post advanced SQL questions here in the future.
i have the following two tables (trimmed down for simplicity's sake): t_inspect id, inspect_timestamp t_inspect_result id, inspect_id, inspect_pass yes, i need both tables, although it might not be obvious since i trimmed down the columns in this simple example. inspect_pass (bool): pass = true, fail = false let's say i have the following values: t_inspect 1, 2006-05-31... 2, 2006-06-01... t_inspect_result 1, 1, true 2, 2, false 3, 2, false 4, 2, false 5, 2, true iow, the first inspection passes the first time, the second inspection (t_inspect.id = 2) had to be inspected 4 times before it pass inspection. you can assume it was reworked inbetween inspections and more defects were found upon reinspection. i'm trying to develop a query that will provide the first pass yield. iow, the yield generated by counting *only* the results associated with the first time a unit is inspected for a given inspect.id. t_inspect_result 1, 1, *true* -- first inspect for t_inspect.id = 1 2, 2, *false* -- first inspect for t_inspect.id = 2 3, 2, false 4, 2, false 5, 2, true specifically, this case would yield 50% (1 pass / 2 total) since the first inspection passed the first time and the second inspection failed the first time. i think i can get the first pass results through a given inspection by using "distinct on (t_inspect.id)..." i say think b/c the actual query is quite complex and i'm not 100% sure my results are consistent with what i'm expecting. i think i can get the results of the entire t_inspect_result table using the count function - get #passes, get #total and do some math. what i can't seem to do is to get both - a count of the total number of t_inspect_result.inspect_pass where the value is true and a total count, by unique t_inspect.id. any guidance would be much appreciated. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
