From: "codeWarrior" <gpatnude@hotmail.com>
Subject: Re: Advanced Query
Date: Tue, 6 Jun 2006 07:48:36 -0700
Organization: iDynaTECH, Inc.
Lines: 88
Message-ID: <e644g5$2b2e$1@news.hub.org>
References: <20060601230921.92601.qmail@web33305.mail.mud.yahoo.com>
Reply-To: "codeWarrior" <gpatnude@hotmail.com>
To: pgsql-sql@postgresql.org

Personally: I think your posts are getting annoying. This isn't SQLCentral. 
Learn to write your own damn queries or even better - buy a book on SQL...


<operationsengineer1@yahoo.com> wrote in message 
news:20060601230921.92601.qmail@web33305.mail.mud.yahoo.com...
> 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
>