Store Yearly AVG in table 2

[larrydavid]

Hello,

Based on these table values, I am trying to do a select to display a yearly average for each ID:

ID	       TestRatio	      Year	Month	MonthName
111111111	50.000000000000	2011	1	    Jan 2011
111111111	27.000000000000	2011	2	    Feb 2011
111111111	35.000000000000	2011	3	    Mar 2011
111111111	45.000000000000	2011	4	    Apr 2011
222222222	25.000000000000	2011	1	    Jan 2011
222222222	25.000000000000	2011	2	    Feb 2011
222222222	40.000000000000	2011	3	    Mar 2011
222222222	23.000000000000	2011	4	    Apr 2011
333333333	12.000000000000	2011	1	    Jan 2011
333333333	45.000000000000	2011	2	    Feb 2011
333333333	67.000000000000	2011	3	    Mar 2011
333333333	15.000000000000	2011	4	    Apr 2011

Instead of this existing ratio, it would be the yearly AVG of this [TestRatio] for each ID group, along with other fields (ID, NAME, Address, etc.).

I know the overall average would be calculated as: AVG([TestRatio]) * 1.0 , but I am not quite sure how to group this for each ID group. I've tried some outer queries and I think I'm close, but not quite there. So, the output I am trying to get is:

ID			TestRatioYearlyAVG		Year	
111111111		43.000000000000		2011		
222222222		28.000000000000		2011		
333333333		37.000000000000		2011

As always, any help is greatly appreciated and learned from.

Thanks,
Larry

 

[jbenson001]

Since I don't have the full table structures of either table you are getting your data from, this is a best guess. I hope it helps, or at least gets you on the right path:

SELECT av.ID, av.YearlyAVG, av.[Year], ot.Name, ot.Address, ...Other columns you want ...
FROM YourOtherTable ot
   INNER JOIN 
   (   SELECT ID, AVG(TestRatio) AS YearlyAVG, [Year]
       FROM TestRatioTable av
       GROUP BY ID, [Year]  
   )av 
   ON ot.ID = av.ID AND ot.[Year] = av.[Year]

 

[jbenson001]

The alias is not needed on this line:

 FROM TestRatioTable [b][red]av[/red][/b]

 

[markros]

SQL 2005+:

select *, AVG(TestRatio) over (partition by ID) as [Group Average]
from myTable

PluralSight Learning Library

 

[larrydavid]

Thank you both for your help. jbenson, I used your solution and it works perfectly. Thank you so much.

Best Regards,
Larry

 

[jbenson001]

Cool, glad it worked. Give the partition By a shot too. That is some neat functionality.