Re: Advanced Query - Mailing list pgsql-sql
From | codeWarrior |
---|---|
Subject | Re: Advanced Query |
Date | |
Msg-id | e644g5$2b2e$1@news.hub.org Whole thread Raw |
In response to | Advanced Query (<operationsengineer1@yahoo.com>) |
Responses |
Re: Advanced Query
|
List | pgsql-sql |
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 >