Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language

Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Average in each row

I have two tables

Table1 (Date,Val) and Table2(Date,Val1,Val2) --val, val1 and val2 are

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.




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

"Pradeep" <agarw@eeism.com> wrote in message


On 29 May, 11:55, "Greg O" <g@nospam.com.au> wrote:

Thanks Greg,

I have tried this but the drawback is that it calculates the avg for
each row, which makes it very slow.




On May 29, 12:03 pm, Pradeep <agarw@eeism.com> wrote:

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

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.
Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc