SQL Server Indexed Views: Cannot create clustered index because the select list contains an expression on result of aggregate function

Question

I am trying to create a simple indexed view on the query below. But when I try to create a unique clustered index on it, I get the following error:

Cannot create the clustered index '..' on view '..' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list.

The query I used is as follows:

SELECT 
    [Manufacturer]
    ,ISNULL(SUM([QAV]),0) as AvgQAV
    ,ISNULL(SUM([BackOrders$]),0)as AvgBackorder$
    ,DATEPART(year,[Date])as Year
    ,DATEPART(month,[Date])as Month
    ,[fixSBU]
    ,[DC Name]
FROM [dbo].[TABLE1]
Group By
    [Manufacturer]      
    ,DATEPART(year,[Date])
    ,DATEPART(month,[Date])
    ,[fixSBU]
    ,[DC Name]

Could anyone tell me the possible cause for this? As you can see I am already using the ISNULL function.

Answer 1

Here is a link to all the restrictions of an index view: https://msdn.microsoft.com/en-us/library/ms191432.aspx#Restrictions

From the documentation these two items should stick out:

• If GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING. These GROUP BY restrictions are applicable only to the indexed view definition. A query can use an indexed view in its execution plan even if it does not satisfy these GROUP BY restrictions.
• If the view definition contains a GROUP BY clause, the key of the unique clustered index can reference only the columns specified in the GROUP BY clause.

Also, you need to change your ISNULL statements. Right now you have ISNULL(SUM([BackOrders$]),0) and it should be SUM(ISNULL([BackOrders$], 0)). You need to SUM the ISNULL, not the other way around.