Thread: Advanced Query
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
On Thu, Jun 01, 2006 at 04:09:21PM -0700, operationsengineer1@yahoo.com wrote: > 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. Are you looking for something like this? SELECT 1.0 * sum(CASE WHEN inspect_pass THEN 1 ELSE 0 END) / count(*) FROM ( SELECT DISTINCT ON (inspect_id) inspect_id, inspect_pass FROM t_inspect_result ORDER BY inspect_id, id ) AS s; Multiply by 100.0 instead of 1.0 if you want percent. If you have a cast from boolean to integer (built-in in 8.1, easily created in earlier versions) then you could replace the CASE expression with a cast (inspect_pass::integer). Whether to use the more explicit CASE or the more concise cast is a matter of style. -- Michael Fuhr
> On Thu, Jun 01, 2006 at 04:09:21PM -0700, > operationsengineer1@yahoo.com wrote: > > 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. > > Are you looking for something like this? > > SELECT 1.0 * sum(CASE WHEN inspect_pass THEN 1 ELSE > 0 END) / count(*) > FROM ( > SELECT DISTINCT ON (inspect_id) inspect_id, > inspect_pass > FROM t_inspect_result > ORDER BY inspect_id, id > ) AS s; > > Multiply by 100.0 instead of 1.0 if you want > percent. > > If you have a cast from boolean to integer (built-in > in 8.1, easily > created in earlier versions) then you could replace > the CASE > expression with a cast (inspect_pass::integer). > Whether to use the > more explicit CASE or the more concise cast is a > matter of style. Michael, wow! i never heard of case or seen the "if / then" style in sql. i need to get out more... or maybe less. ;-) i have been working through a simplified version of the problem and i am accurately getting the "pieces" of data that i need (#pass, #total) - it is similar to your example following your first FROM statement. i just need to work the complexities back in w/o destroying my current results and then perform the math on the results - either in pgsql or in my app. i'll play around with the more advanced stuff tomorrow. thanks - i think i have enough pieces to get this done. if not... "i'll be baaawck." __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
On Fri, Jun 02, 2006 at 10:09:01AM -0700, operationsengineer1@yahoo.com wrote: > Michael, my derivative of your query example works > great - thank you! > > i think i understand everything except why multiplying > by 1.0 is necessary. when i take it out, my expected > result, 0.50000000000..., turns into 0 - so i assume > it has to do with formatting the result. Integer division yields integers, so 1 / 2 = 0 whereas 1.0 / 2.0 = 0.5. The expression therefore needs something to force a non-integer result. Multiplying by 1.0 (or 100.0) is one way; using 1.0 and 0.0 in the CASE expression is another; casting one of the operands to numeric or one of the floating-point types is yet another. -- Michael Fuhr
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 >
> 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... Personally: (being a newbie with an interest in developing a strong rdms skillset) I've enjoyed following threads like these. Even when the questions (to some) seems overly simplistic, the courteous respondents often share insightful solutions or nuances that are not found in an "off the self" SQL book. However, if questions like these are *really* off-topic for the pgsql-sql I would be interested in knowing what kind of threads are acceptable and on-topic for this list. Also, if there are other mailing lists (pg or other) that are better suited for threads like this, I would appreciate learning of them. Regards, Richard Broersma
Richard Broersma Jr wrote: >> 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... >> > > Personally: (being a newbie with an interest in developing a strong rdms skillset) I've enjoyed > following threads like these. Even when the questions (to some) seems overly simplistic, the > courteous respondents often share insightful solutions or nuances that are not found in an "off > the self" SQL book. > > However, if questions like these are *really* off-topic for the pgsql-sql I would be interested in > knowing what kind of threads are acceptable and on-topic for this list. > > Also, if there are other mailing lists (pg or other) that are better suited for threads like this, > I would appreciate learning of them. > > Regards, > > Richard Broersma > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > I would like to say that I have found the level of patience and help offered by the members of the mailing lists to be a key factor in my choice to use Postgres. And I feel that the response above would have been the sort of thing that would have turned me off., even if it was not in response to one of my own posts. Sometimes I may not understand the questions being asked or the answers being given but it sure is great to be able to search and find them later when you are the one hitting that same wall. If someone on a list like this rubs you the wrong way, personally I would ignore them, hence I expect to get zero responses to this! ;) Just my 2c this is a great resource and I would hope all feel as welcome here as I have, Oisin
I would hope that your choice to use postgreSQL is because it is superior technology that scales well financially... not because you get a warm fuzzy from all your friends on the mailing lists... "Oisin Glynn" <me@oisinglynn.com> wrote in message news:4485A237.4070708@oisinglynn.com... > Richard Broersma Jr wrote: >>> 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... >>> >> >> Personally: (being a newbie with an interest in developing a strong rdms >> skillset) I've enjoyed >> following threads like these. Even when the questions (to some) seems >> overly simplistic, the >> courteous respondents often share insightful solutions or nuances that >> are not found in an "off >> the self" SQL book. >> >> However, if questions like these are *really* off-topic for the pgsql-sql >> I would be interested in >> knowing what kind of threads are acceptable and on-topic for this list. >> >> Also, if there are other mailing lists (pg or other) that are better >> suited for threads like this, >> I would appreciate learning of them. >> >> Regards, >> >> Richard Broersma >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 1: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly >> > I would like to say that I have found the level of patience and help > offered by the members of the mailing lists to be a key factor in my > choice to use Postgres. And I feel that the response above would have been > the sort of thing that would have turned me off., even if it was not in > response to one of my own posts. Sometimes I may not understand the > questions being asked or the answers being given but it sure is great to > be able to search and find them later when you are the one hitting that > same wall. If someone on a list like this rubs you the wrong way, > personally I would ignore them, hence I expect to get zero responses to > this! ;) > > Just my 2c this is a great resource and I would hope all feel as welcome > here as I have, > > Oisin > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
On Tue, 2006-06-06 at 10:30, Richard Broersma Jr wrote: > > 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... > > Personally: (being a newbie with an interest in developing a strong rdms skillset) I've enjoyed > following threads like these. Even when the questions (to some) seems overly simplistic, the > courteous respondents often share insightful solutions or nuances that are not found in an "off > the self" SQL book. > > However, if questions like these are *really* off-topic for the pgsql-sql I would be interested in > knowing what kind of threads are acceptable and on-topic for this list. > > Also, if there are other mailing lists (pg or other) that are better suited for threads like this, > I would appreciate learning of them. Personally, I too enjoy these threads. And when I find someone annoying (not you, by the way) I just ignore them. It's a modern world. there are these things called filters. I use them to ignore people that annoy me, rather than demanding they stop posting. Like my momma says, if you can't say anything nice, don't say anything at all. Keep posting. If I don't wanna read / answer your posts, I won't. If I've got 5 free minutes, I will. I suggest codeWarrier do the same.
On Tue, Jun 06, 2006 at 08:30:54AM -0700, Richard Broersma Jr wrote: > > However, if questions like these are *really* off-topic for the > pgsql-sql I would be interested in knowing what kind of threads are > acceptable and on-topic for this list. They're not off-topic. The point of the list is indeed to answer "how do I do this with SQL"? If one doesn't want to see such questions, one might unsubscribe. -- Andrew Sullivan | ajs@crankycanuck.ca A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton
On Tue, Jun 06, 2006 at 09:20:13AM -0700, codeWarrior wrote: > I would hope that your choice to use postgreSQL is because it is superior > technology that scales well financially... not because you get a warm fuzzy > from all your friends on the mailing lists... I would hope that the tone of the mailing lists might be maintained in much the high one demonstrated by such polite, helpful, and smarter-than-me people as those on the PostgreSQL core team. I note that the above troll does not qualify. I suggest people avoid feeding it. A -- Andrew Sullivan | ajs@crankycanuck.ca Information security isn't a technological problem. It's an economics problem. --Bruce Schneier
Don't forget that support is a very important part of making a decision about whether to or not to use a technology. Having people who are happy to read and respond to any question is part of great support for the product.
And I am glad to see that most people on this list agree with me on the importance of even the basic questions like this.
What was the original question again? Heh.
-Aaron
And I am glad to see that most people on this list agree with me on the importance of even the basic questions like this.
What was the original question again? Heh.
-Aaron
On 6/6/06, codeWarrior <gpatnude@hotmail.com> wrote:
I would hope that your choice to use postgreSQL is because it is superior
technology that scales well financially... not because you get a warm fuzzy
from all your friends on the mailing lists...
On Jun 6, 2006, at 12:32 PM, Andrew Sullivan wrote: > On Tue, Jun 06, 2006 at 09:20:13AM -0700, codeWarrior wrote: >> I would hope that your choice to use postgreSQL is because it is >> superior >> technology that scales well financially... not because you get a >> warm fuzzy >> from all your friends on the mailing lists... > > I would hope that the tone of the mailing lists might be maintained > in much the high one demonstrated by such polite, helpful, and > smarter-than-me people as those on the PostgreSQL core team. I note > that the above troll does not qualify. I suggest people avoid > feeding it. update thread set response = response + 1; > > A > > -- > Andrew Sullivan | ajs@crankycanuck.ca > Information security isn't a technological problem. It's an economics > problem. > --Bruce Schneier > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster -- Daryl self email: ( daryl at: eddl dot: us )