Sql Server Programming :: Average in each row


	Average in each row I have two tables Table1 (Date,Val) and Table2(Date,Val1,Val2) --val, val1 and val2 are integers I want to write a query wherein i take the date interval and for each date in the date interval i display Date,Val1,Val2, Avg(Val) --Avg(val) is for the whole interval i.e the Avg is same and repeated for all the rows. The only way I could think of is that I calculated the average for the interval in a seperate query and used that query in a cross join with Table2. but i takes some time to do the cross join. Can anyone suggest a better solution. Thanks Pradeep Hi, Select Date, Val1, Val2, (select avg(val) from table1 where table1.date=table2.date) from table2 - Greg O SQL Server Documentation the easy way SQL 2000, SQL 2005 the best is back http://www.geckoware.com.au/Content.aspx?Doc_id=1001 "Pradeep" <agarw@eeism.com> wrote in message news:1180419071.746187.221740@q66g2000hsg.googlegroups.com... - Hide quoted text - >I have two tables > Table1 (Date,Val) and Table2(Date,Val1,Val2) --val, val1 and val2 are > integers > I want to write a query wherein i take the date interval and for each > date in the date interval i display > Date,Val1,Val2, Avg(Val) --Avg(val) is for the whole interval i.e the > Avg is same and repeated for all the rows. > The only way I could think of is that I calculated the average for the > interval in a seperate query and used that query in a cross join with > Table2. but i takes some time to do the cross join. > Can anyone suggest a better solution. > Thanks > Pradeep On 29 May, 11:55, "Greg O" <g@nospam.com.au> wrote: - Hide quoted text - > Hi, > Select Date, Val1, Val2, (select avg(val) from table1 where > table1.date=table2.date) from table2 > - > Greg O > SQL Server Documentation the easy way > SQL 2000, SQL 2005 the best is backhttp://www.geckoware.com.au/Content.aspx?Doc_id=1001 > "Pradeep" <agarw@eeism.com> wrote in message > news:1180419071.746187.221740@q66g2000hsg.googlegroups.com... > >I have two tables > > Table1 (Date,Val) and Table2(Date,Val1,Val2) --val, val1 and val2 are > > integers > > I want to write a query wherein i take the date interval and for each > > date in the date interval i display > > Date,Val1,Val2, Avg(Val) --Avg(val) is for the whole interval i.e the > > Avg is same and repeated for all the rows. > > The only way I could think of is that I calculated the average for the > > interval in a seperate query and used that query in a cross join with > > Table2. but i takes some time to do the cross join. > > Can anyone suggest a better solution. > > Thanks > > Pradeep- Hide quoted text - > Thanks Greg, I have tried this but the drawback is that it calculates the avg for each row, which makes it very slow. Thanks Pradeep -----------------------------------------------Reply----------------------------------------------- On May 29, 12:03 pm, Pradeep <agarw@eeism.com> wrote: - Hide quoted text - > On 29 May, 11:55, "Greg O" <g@nospam.com.au> wrote: > > Hi, > > Select Date, Val1, Val2, (select avg(val) from table1 where > > table1.date=table2.date) from table2 > > - > > Greg O > > SQL Server Documentation the easy way > > SQL 2000, SQL 2005 the best is backhttp://www.geckoware.com.au/Content.aspx?Doc_id=1001 > > "Pradeep" <agarw@eeism.com> wrote in message > >news:1180419071.746187.221740@q66g2000hsg.googlegroups.com... > > >I have two tables > > > Table1 (Date,Val) and Table2(Date,Val1,Val2) --val, val1 and val2 are > > > integers > > > I want to write a query wherein i take the date interval and for each > > > date in the date interval i display > > > Date,Val1,Val2, Avg(Val) --Avg(val) is for the whole interval i.e the > > > Avg is same and repeated for all the rows. > > > The only way I could think of is that I calculated the average for the > > > interval in a seperate query and used that query in a cross join with > > > Table2. but i takes some time to do the cross join. > > > Can anyone suggest a better solution. > > > Thanks > > > Pradeep- Hide quoted text - > > > Thanks Greg, > I have tried this but the drawback is that it calculates the avg for > each row, which makes it very slow. > Thanks > Pradeep- Hide quoted text - > Try. I assume you want value between two dates date1 and date 2 select a.date ,a.val1,a.val2 ,b.avgval from table2 a left outer join -- ?? ( u want full outer join ?) (select date,avg(val) as avgval where date between date1 and date2 group by date ) b on a.date = b.date where a.date between date1 and date2 order by a.date Select Date, Val1, Val2, (select avg(val) from table1 where - Hide quoted text - > > table1.date=table2.date) from table2 Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it.