Re: [SQL] Add calculated fields from one table to other table
Hi Thanks a lot for your help. The query which you suggested gives me a result like this A | 12:00| 12 | 64.99 | 63.99 | 12:01 | 0 | | A | 12:02 | 5 | 36.99 | 32.99 but I wanted the result to look like this A | 12:00| 12 | 64.99 | 63.99 A | 12:01 | 0 | | A | 12:02 | 5 | 36.99 | 32.99 Can anybody help me with that. Thanks in advance Roopa Moiz Kothari <[EMAIL PROTECTED]> wrote: Hi Roopa,If your timeseries table has records for all minutes, then you should outer join both tables so as to get the desired results you are looking for... try doing this.select tk.ric, tm.timeseries_time , count(tk.*), avg(tk.price),sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume)from ticks tk right outer join timeseries tm where tk.tick_time >= tm.timeseries_time and tk.tick_time < (tm.timeseries_time + '1 minute' :: interval)::time group by tm.timeseries_time,tk.ric order by tk.ric, tm.timeseries_time On 10/30/06, roopa perumalraja < [EMAIL PROTECTED]> wrote:Hi Thanks a lot for your immediate reply. I want to explain more about it. The ticks table has many rows in each minute and timeseries table has 1 minute increment data. And the query is as mentioned below, which just displays the result for the minutes in which the tick data exists. but i would like the result for the query to be like ric | time | count | avg_price | avg_volume A | 12:00| 12 | 64.99 | 63.99 A | 12:01 | 0 | | A | 12:02 | 5 | 36.99 | 32.99 but my query result is just A | 12:00| 12 | 64.99 | 63.99 A | 12:02 | 5 | 36.99 | 32.99 so can you help me out to modify the query to get the result what I expect select tk.ric, tm.timeseries_time, count(tk.*), avg(tk.price),sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from ticks tk, timeseries tm where tk.tick_time >= tm.timeseries_time and tk.tick_time < (tm.timeseries_time + '1 minute' :: interval)::time group by tm.timeseries_time,tk.ric order by tk.ric, tm.timeseries_time Thanks a lot in advance Roopa ps. I wrote the query from your idea. so tanks a lot Richard Broersma Jr <[EMAIL PROTECTED]> wrote:> Thanks a lot for your help. The query does work, but now I have a problem. The query goes like > this: > > select tk.ric, tm.timeseries_time, count(tk.*), avg(tk.price),> sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) > from ticks tk, timeseries tm where tk.tick_time >= tm.timeseries_time and > tk.tick_time < (tm.timeseries_time + '1 minute' :: interval)::time group by tm.timeseries_time,> tk.ric order by tk.ric, tm.timeseries_time> > The problem is, if there is no row for certain minute, then I want the count to be displayed > as zero and other coulmns like avg to be null. In this query, it just omits those minutes which> doesnt have any row for a particular minute.You have to use an outer join. You will need a table or sequence that has every minute in a range that you are interested in and outer join that to your actual table. This will give you a countof zero.i.e.select S.minute, count(W.minute) as minutecntfrom Series_of_Minutes S left join Working_table W on S.minute = W.minute;hope this helps.REgards,Richard Broersma jr.ps. sorry that my query suggestion didn't work :0) Want to start your own business? Learn how on Yahoo! Small Business. Get your email and see which of your friends are online - Right on the new Yahoo.com
Re: [SQL] Add calculated fields from one table to other table
Hi Thanks a lot for your help. The query which you suggested goes like this select foo.ric, tm.times_time, count(tk.*), avg(tk.price), sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select distinct ric from ticks_20060404 where ric = 'TRB') as foo, times tm left join ticks_20060404 tk on tk.tick_time >= tm.times_time and tk.tick_time < (tm.times_time + '1 minute' :: interval)::time and tk.ric = 'TRB' group by tm.times_time, foo.ric order by tm.times_time; which gives me a result like this TRB | 12:00| 12 | 64.99 | 63.99 TRB | 12:01 | 0 | | TRB | 12:02 | 5 | 36.99 | 32.99 but I wanted the result for all the ric to be displayed. If I write the query like this select foo.ric, tm.times_time, count(tk.*), avg(tk.price), sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select distinct ric from ticks) as foo, times tm left join ticks tk on tk.tick_time >= tm.times_time and tk.tick_time < (tm.times_time + '1 minute' :: interval)::time and tk.ric = foo.ric group by tm.times_time, foo.ric order by tm.times_time; I get a error message like this: ERROR: invalid reference to FROM-clause entry for table "foo"HINT: There is an entry for table "foo", but it cannot be referenced from this part of the query. Can you help me with this? Thanks in advance RoopaRichard Broersma Jr <[EMAIL PROTECTED]> wrote: > Thanks a lot for your help. The query does work, but now I have a problem. The query goes like> this: > > select tk.ric, tm.timeseries_time, count(tk.*), avg(tk.price),> sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) > from ticks tk, timeseries tm where tk.tick_time >= tm.timeseries_time and > tk.tick_time < (tm.timeseries_time + '1 minute' :: interval)::time group by tm.timeseries_time,> tk.ric order by tk.ric, tm.timeseries_time> > The problem is, if there is no row for certain minute, then I want the count to be displayed> as zero and other coulmns like avg to be null. In this query, it just omits those minutes which> doesnt have any row for a particular minute.You have to use an outer join. You will need a table or sequence that has every minute in a rangethat you are interested in and outer join that to your actual table. This will give you a countof zero.i.e.select S.minute, count(W.minute) as minutecntfrom Series_of_Minutes S left join Working_table Won S.minute = W.minute;hope this helps.REgards,Richard Broersma jr.ps. sorry that my query suggestion didn't work :0) We have the perfect Group for you. Check out the handy changes to Yahoo! Groups.
Re: [SQL] Add calculated fields from one table to other table
> select foo.ric, tm.times_time, count(tk.*), avg(tk.price), > sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select distinct > ric from ticks) as > foo, times tm left join ticks tk on tk.tick_time >= tm.times_time and > tk.tick_time < > (tm.times_time + '1 minute' :: interval)::time and tk.ric = foo.ric group by > tm.times_time, > foo.ric order by tm.times_time; > > I get a error message like this: > > ERROR: invalid reference to FROM-clause entry for table "foo" > HINT: There is an entry for table "foo", but it cannot be referenced from > this part of the > query. > > Can you help me with this? I will try, but to start with, to help us, when you have a difficult query to solve, you should simplify your query as much a possible. This way we can more quickly see what you are intending verses the problem you are having. 1 tip: (select distinct ric from ticks) I think that you will find that: (select ric from ticks group by ric) is much faster than using the distinct. The error in the query that I see is that you are using foo as a criteria in the ON syntax. This will not work. To illistrate: A,B join C ON (B.id = C.id) --ON syntax only works with joins AND (B.id2 < C.id) --The And is still part of the ON syntax --you can not reference A since it is not joined Where A.id = B.id --you can only specify a non-joined tables contrainst AND A.id2 < C.id2 ; --in the where clause I hope this helps. Regards, Richard Broersma JR. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Case Preservation disregarding case sensitivity?
On Friday 27 October 2006 19:38, Joe wrote: > Hi Beau, > > On Fri, 2006-10-27 at 16:23 -0700, beau hargis wrote: > > I am hoping that there is an easy way to obtain case-preservation with > > case-insensitivity, or at the very least, case-preservation and complete > > case-sensitivity, or case-preservation and a consistant case-conversion > > strategy. > > > > The case of the column names need to be preserved because that is the way > > the schema is designed and most importantly (VERY, VERY IMPORTANT), > > column names are used in apps as hash values, or as named references > > which are case sensitive and as such need to be delivered to the client > > in exactly in the manner specified at the time of table creation. > > > > I went through the same issue in my conversion from MySQL to Postgres > and (since I had a small application) I ended up changing up all my > tables and columns "UserProfile" to user_profile. > > I'm afraid however, that it's MySQL that is the odd man out. I haven't > researched this completely but I believe PG follows either the FIPS-127 > or SQL-92 standard with respect to what are called "delimited > identifiers". Basically, this says if you want case sensitivity in > identifier names, you have to use double quotes wherever you refer to > the identifier. Without the double quotes, the SQL implementor can > either use UPPERCASE (as I believe Oracle and DB2 do) or lowercase (as > PG does) when it displays those identifiers. > > Joe Again, I am at the same point I was at when searching and searching for information on the problem, and I am not sure what the SQL standard has to say about it: I do not, and I dont think that anyone else who is struggling to deal with the problem does, care about case-sensitivity. I am interested in case preservation of column names. I do, indeed, want identifiers treated in a case insensitive way, but I want the case PRESERVED in the table definitions and I want that case, as preserved, to be reflected in the field names as returned by the server to any client library that connects and initiates a query. Case-preservation is not the same as case-sensitivity; nor is case-normalization the same as case-insensitivity. What PostgreSQL is doing is converting any, and all, identifiers to a lower case and then matching those against the identifiers (as stored in the table definition) in a case-sensitive manner. It 'normalizes' the case of the identifiers so that it has a common internal representation; the desires of the programmer and database architect be damned. Referenced specification details: From FIPS-127: === 3. Delimited identifiers. In the previous ANSI SQL specification, it was not possible for an application to specify identifiers with spaces or other special symbols. Also, it was not possible to protect against future assaults on the name space for (identifier) by additions to the (reserved word) list. The new facility for (delimited identifier) allows a user to enclose all identifiers in double-quotation marks, thereby ensuring that the name defined or referenced may contain spaces or other special symbols and will not be impacted by future additions to the (reserved word) list. === From SQL-92/Sec. 5.2: === 10)The of a is equivalent to an in which every letter that is a lower- case letter is replaced by the equivalent upper-case letter or letters. This treatment includes determination of equiva- lence, representation in the Information and Definition Schemas, representation in the diagnostics area, and similar uses. 11)The of a (with every letter that is a lower-case letter replaced by the equivalent upper-case letter or letters), treated as the repetition of a that specifies a of SQL_TEXT, shall not be equal, according to the comparison rules in Subclause 8.2, "", to any (with every letter that is a lower-case letter replaced by the equivalent upper-case letter or letters), treated as the repetition of a that specifies a of SQL_TEXT. 12)Two s are equivalent if their s, considered as the repetition of a that specifies a of SQL_TEXT, compare equally according to the comparison rules in Subclause 8.2, "". 13)A and a are equiva- lent if the of the (with every letter that is a lower-case letter replaced by the equiva- lent upper-case letter or letters) and the of the (with all occurrences of replaced by and all occurrences of replaced by ), considered as the repetition of a that specifies a of SQL_TEXT and an implementation- defined collation that is sensitive to case, compare equally according to the comparison rules in Subclause 8.2, "". 14)Two s are equivalent if their s (with all occurrences of replaced by and all occurrences of replaced by ), considered as the repetition of a that specifies a of SQL_TEXT and an implementation-defined collation that is sensitive to case, compare
Re: [SQL] Case Preservation disregarding case sensitivity?
beau hargis <[EMAIL PROTECTED]> writes: > Considering the differences that already exist between database systems and > their varying compliance with SQL and the various extensions that have been > created, I do not consider that the preservation of case for identifiers > would violate any SQL standard. That's not how I read the spec. It is true that we are not 100% spec compliant, but that isn't a good argument for moving further away from spec. Not to mention breaking backwards compatibility with our historical behavior. The change you propose would fix your application at the cost of breaking other people's applications. Perhaps you should consider fixing your app instead. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Add calculated fields from one table to other table
Roopa,Why dont you try putting in some case or decode with your first field, so incase if nothing is returned you explicitly make it 'A' kinds. Regards,Moiz Kothari On 10/31/06, roopa perumalraja <[EMAIL PROTECTED]> wrote: Hi Thanks a lot for your help. The query which you suggested gives me a result like this A | 12:00| 12 | 64.99 | 63.99 | 12:01 | 0 | | A | 12:02 | 5 | 36.99 | 32.99 but I wanted the result to look like this A | 12:00| 12 | 64.99 | 63.99 A | 12:01 | 0 | | A | 12:02 | 5 | 36.99 | 32.99 Can anybody help me with that. Thanks in advance Roopa Moiz Kothari <[EMAIL PROTECTED]> wrote: Hi Roopa,If your timeseries table has records for all minutes, then you should outer join both tables so as to get the desired results you are looking for... try doing this.select tk.ric, tm.timeseries_time , count(tk.*), avg(tk.price),sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume)from ticks tk right outer join timeseries tm where tk.tick_time >= tm.timeseries_time and tk.tick_time < (tm.timeseries_time + '1 minute' :: interval)::time group by tm.timeseries_time,tk.ric order by tk.ric, tm.timeseries_time On 10/30/06, roopa perumalraja < [EMAIL PROTECTED]> wrote: Hi Thanks a lot for your immediate reply. I want to explain more about it. The ticks table has many rows in each minute and timeseries table has 1 minute increment data. And the query is as mentioned below, which just displays the result for the minutes in which the tick data exists. but i would like the result for the query to be like ric | time | count | avg_price | avg_volume A | 12:00| 12 | 64.99 | 63.99 A | 12:01 | 0 | | A | 12:02 | 5 | 36.99 | 32.99 but my query result is just A | 12:00| 12 | 64.99 | 63.99 A | 12:02 | 5 | 36.99 | 32.99 so can you help me out to modify the query to get the result what I expect select tk.ric, tm.timeseries_time, count(tk.*), avg(tk.price),sum(tk.price*tk.volume )/sum(tk.volume), sum(tk.volume) from ticks tk, timeseries tm where tk.tick_time >= tm.timeseries_time and tk.tick_time < (tm.timeseries_time + '1 minute' :: interval)::time group by tm.timeseries_time,tk.ric order by tk.ric, tm.timeseries_time Thanks a lot in advance Roopa ps. I wrote the query from your idea. so tanks a lot Richard Broersma Jr < [EMAIL PROTECTED]> wrote: > Thanks a lot for your help. The query does work, but now I have a problem. The query goes like > this: > > select tk.ric, tm.timeseries_time, count(tk.*), avg(tk.price),> sum(tk.price*tk.volume )/sum(tk.volume), sum(tk.volume) > from ticks tk, timeseries tm where tk.tick_time >= tm.timeseries_time and > tk.tick_time < (tm.timeseries_time + '1 minute' :: interval)::time group by tm.timeseries_time,> tk.ric order by tk.ric, tm.timeseries_time> > The problem is, if there is no row for certain minute, then I want the count to be displayed > as zero and other coulmns like avg to be null. In this query, it just omits those minutes which > doesnt have any row for a particular minute.You have to use an outer join. You will need a table or sequence that has every minute in a range that you are interested in and outer join that to your actual table. This will give you a count of zero.i.e.select S.minute, count(W.minute) as minutecntfrom Series_of_Minutes S left join Working_table W on S.minute = W.minute;hope this helps.REgards,Richard Broersma jr. ps. sorry that my query suggestion didn't work :0) Want to start your own business? Learn how on Yahoo! Small Business. Get your email and see which of your friends are online - Right on the new Yahoo.com
Re: [HACKERS] [SQL] Case Preservation disregarding case sensitivity?
"Chuck McDevitt" <[EMAIL PROTECTED]> writes: > At Teradata, we certainly interpreted the spec to allow case-preserving, > but case-insensitive, identifiers. Really? As I see it, the controlling parts of the SQL spec are (SQL99 sec 5.2) 26) A and a are equivalent if the of the (with every letter that is a lower-case letter replaced by the corresponding upper-case letter or letters) and the of the (with all occurrences of replaced by and all occurrences of replaced by ), considered as the repetition of a that specifies a of SQL_IDENTIFIER and an implementation-defined collation that is sensitive to case, compare equally according to the comparison rules in Subclause 8.2, "". 27) Two s are equivalent if their s, considered as the repetition of a that specifies a of SQL_IDENTIFIER and an implementation-defined collation that is sensitive to case, compare equally according to the comparison rules in Subclause 8.2, "". Note well the "sensitive to case" bits there. Now consider CREATE TABLE tab ( "foobar" int, "FooBar" timestamp, "FOOBAR" varchar(3) ); We can *not* reject this as containing duplicate column names, else we have certainly violated rule 27. Now what will you do with SELECT fooBar FROM tab; ? The spec is unquestionably on the side of "you selected the varchar column"; historical Postgres practice is on the side of "you selected the int column". AFAICS a case-insensitive approach would have to fail with some "I can't identify which column you mean" error. I am interested to see where you find support for that in the spec... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Add calculated fields from one table to other table
Hi Richard, Thanks for your help. That does make sense, but I am not able to get the result what I wanted exactly. Let me explain you. I have ticks table in which I have columns like ric, tick_time, price & volume. The times table has just one column with times_time which has time data for each minute ie.) Ticks ric | tick_time | price | volume A | 12:00:01 | 23.00 | 12 A | 12:00:02 | 26.00 | 7 B | 12: 00:02 | 8.00 | 2 B | 12:01:01 | 45.00 | 6 Times times_time 12:00 12:01 12:02 Now I want the timeseries for each minute for all ric in the tick table. So my query goes like this for a particular ric say for example ric 'A' select foo.ric, tm.times_time, count(tk.*), avg(tk.price), sum (tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select ric from ticks where ric = 'A' group by ric) as foo, times tm left join ticks tk on tk.tick_time >= tm.times_time and tk.tick_time < (tm.times_time + '1 minute' :: interval)::time and tk.ric = 'A' group by tm.times_time, foo.ric order by tm.times_time; I get the result as I expect, but i am not able to derive a query for all rics in the tick table. I really appreciate your help. Regards Roopa Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > select foo.ric, tm.times_time, count(tk.*), avg(tk.price),> sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select distinct ric from ticks) as> foo, times tm left join ticks tk on tk.tick_time >= tm.times_time and tk.tick_time <> (tm.times_time + '1 minute' :: interval)::time and tk.ric = foo.ric group by tm.times_time,> foo.ric order by tm.times_time;> > I get a error message like this:> > ERROR: invalid reference to FROM-clause entry for table "foo"> HINT: There is an entry for table "foo", but it cannot be referenced from this part of the> query.> > Can you help me with this?I will try, but to start with, to help us, when you have a difficult query to solve, you shouldsimplify your query as much a possible. This way we can more quickly see what you are intendingverses the problem you are having.1 tip: (select distinct ric from ticks)I think that you will find that:(select ric from ticks group by ric)is much faster than using the distinct.The error in the query that I see is that you are using foo as a criteria in the ON syntax. Thiswill not work. To illistrate:A,B join CON (B.id = C.id) --ON syntax only works with joinsAND (B.id2 < C.id) --The And is still part of the ON syntax--you can not reference A since it is not joinedWhereA.id = B.id --you can only specify a non-joined tables contrainstANDA.id2 < C.id2; --in the where clauseI hope this helps.Regards,Richard Broersma JR. We have the perfect Group for you. Check out the handy changes to Yahoo! Groups.
