Problem with indexed view msql

Question

hi

I have problem with a counting column in my view.

SELECT ColumnC, ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) AS cBig FROM dbo.T1
GROUP BY ColumnC, ModuloColAColB

Query is similar to this MSDN example: http://msdn.microsoft.com/en-us/library/ms191432.aspx

When I try to compile view I received error message like: "invalid column name ModuloColAColB"

So I change group by column names:

SELECT ColumnC, ColumnA % ColumnB AS ModuloColAColB,
COUNT_BIG(*) AS cBig FROM dbo.T1
GROUP BY ColumnC, ColumnA, ColumnB

View is compiling correctly but when I try to add index I receiv the error:

"Cannot create the clustered index 'px_test' on view 'l' 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"

When I remove " ColumnA % ColumnB AS ModuloColAColB" all works fine.

Any suggestion? thanks for advice.

Database version: SQL server 2005 EE.

Answer 1

Try GROUP BY ColumnC, ColumnA % ColumnB ?

From your link:

..cannot contain... An expression on a column used in the GROUP BY clause, or an expression on the results of an aggregate.

I think this means you can't modulo on your group by columns. However, you should be able to do the expression in the GROUP BY and simply repeat in the select clause