DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=iEpY9WIejm/MZszs0ElFZxyri9nLrlv6Egi3dQKNPRrEkf9ZKGuG0z8OiebdI4h95AiGE3BVB/3NKuPd/eICqwv8jiFITvCidhNCpnJEiFknikK8/7DnoSqjw3NmzxYpfiVE+fgVWlzRo/a6yfz7GU+43Lm/5d3a0JYfZ44VjeU=
	;
Message-ID: <20060601230921.92601.qmail@web33305.mail.mud.yahoo.com>
Date: Thu, 1 Jun 2006 16:09:21 -0700 (PDT)
From: <operationsengineer1@yahoo.com>
Subject: Advanced Query
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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