AVG using ROW_NUMBER

I'm using SQL Server 2005, sp 2. My query is below. What I want to see for the results is the average of all of partition 1, the average of partition 2, etc.Does anybody know how I can get this?

SELECT ROW_NUMBER() OVER (PARTITION BY Shop.Location_Code ORDER BY Shop.Date_Code) AS [PARTITION],

(Score) AS [This Year], Shop.Date_Code, Shop.Location_Code

FROM ETL.Transform_FactOpsMSScorecard SHOP INNER JOIN DW_DatamartDB.dbo.DimDate DD

ON Shop.Date_Code=DD.Date_Code

INNER JOIN DW_DatamartDB.dbo.DimLocation LOC ON

Shop.Location_Code = Loc.Location_Code

WHERE District_Code = (@.District)

Results:

Partition This Year Date Code Location Code

1 .85 20070101 1

2 .58 20070509 1

1 .52 20070808 2

2 .54 20070905 2

3 .26 20070104 3

3 .26 20070905 3

Is this what you're looking for?

Code Snippet

select

Partition

,(sum(Thisyear)/count(*)) 'ThisYearAvg'

from

(

SELECT ROW_NUMBER() OVER (PARTITION BY Shop.Location_Code ORDER BY Shop.Date_Code) AS [PARTITION],

(Score) AS [This Year], Shop.Date_Code, Shop.Location_Code

FROM ETL.Transform_FactOpsMSScorecard SHOP INNER JOIN DW_DatamartDB.dbo.DimDate DD

ON Shop.Date_Code=DD.Date_Code

INNER JOIN DW_DatamartDB.dbo.DimLocation LOC ON

Shop.Location_Code = Loc.Location_Code

WHERE District_Code = (@.District)

) a

group by

Partition

|||

The average of what?

select

...,

avg(Score) over(partition by Shop.Location_Code) as avg_score

from

....

AMB

|||

Yes Anthony, That's exactly what I was looking for. Thank you so much for your help!

Lindsay