title: Cannot create an indexed view because I reference a derived table
body: <p>I perform a nested SELECT to create a derived table, <code>hourly</code>, that gets me hourly averages of the availability of a feature. Using <code>hourly</code>, I average all those values to obtain the daily average, and I want to create an indexed view of the daily averages. My query is as follows:</p>
  
  <pre><code>CREATE VIEW DailyView WITH SCHEMABINDING AS
  SELECT hourly.Feature,
         AVG(hourly.AvgAvailability) AS AvgAvailability,
         CAST(hourly.DateTime AS date) AS Date FROM
         (SELECT DISTINCT 
                          SC.Feature,  
                          AVG(SA.Availability) AS AvgAvailability, 
                          SA.DateAndHour AS DateTime
         FROM   dbo.ServiceAvailability AS SA LEFT OUTER JOIN
                dbo.ServiceCatalog AS SC ON SA.ServiceID = SC.ServiceID
  GROUP BY SC.Feature, SA.DateAndHour) hourly
  GROUP BY hourly.Feature, CAST(hourly.DateTime AS date)
  GO
  CREATE UNIQUE CLUSTERED INDEX IDX_V1 ON DailyView(Date)
  GO
  </code></pre>
  
  <p>However, I cannot create an indexed view this way as I get the following error:</p>
  
  <blockquote>
    <p>Cannot create index on view "Reporting.dbo.DailyView" because it references derived table "hourly" (defined by SELECT statement in FROM clause). Consider removing the reference to the derived table or not indexing the view.</p>
  </blockquote>
  
  <p>It is important for me to index this view as we will be dealing with large amounts of data, but I am not sure how to get the nested average without the derived table in there.</p>