SQL Server - Creating a View for an Aggregate Queries
Create View on an Aggregate Query
A view can be created on any type of SQL SELECT statement. There is nothing different or special about creating a view for a query that does data aggregation. Below is an example.
IF ( OBJECT_ID('vwSalesByProduct2007') IS NOT NULL ) DROP VIEW dbo.vwSalesByProduct2007 GO CREATE VIEW vwSalesByProduct2007 AS SELECT DimProductCategory.EnglishProductCategoryName AS "Category", DimProductSubcategory.EnglishProductSubcategoryName AS "Subcategory", DimProduct.EnglishProductName AS "Product", SUM(FactInternetSales.OrderQuantity) AS "TotQty", SUM(FactInternetSales.SalesAmount) AS "TotSales" FROM DimProduct INNER JOIN DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOIN FactInternetSales ON DimProduct.ProductKey = FactInternetSales.ProductKey WHERE OrderDateKey >= 20070101 AND OrderDateKey < 20080000 GROUP BY DimProductCategory.EnglishProductCategoryName, DimProductSubcategory.EnglishProductSubcategoryName, DimProduct.EnglishProductName GO
In development I prefer use the syntax...
...to drop and rebuild the view. Such syntax in a production space could become a problem. Having said this you should have good processes in place to prevent rogue and unwanted code from being introduced into your production system.
IF ( OBJECT_ID('vwSalesByProduct2007') IS NOT NULL ) DROP VIEW dbo.vwSalesByProduct2007 GO
Running the View
SELECT TOP 1000 [Category] ,[Subcategory] ,[Product] ,[TotQty] ,[TotSales] FROM [AdventureWorksDW2008R2].[dbo].[vwSalesByProduct2007]
You will also note that this view has row restriction on it. One can construct views and then leave it up to the consumer to add the appropriate row restriction. However, we are not able to do this with an aggregate view as the WHERE statement precedes the GROUP BY clause. Also, the very nature of how an aggreate query is processed also makes variable row restriction in an aggregate view not possible.
Now you might ask, can I add a HAVING clause to my view at runtime? I would counter and say there is not need to do this. Here is an alternative solution
Construct your aggregate query so that is brings back the most atomic recordset possible. Then you can just add a plain old WHERE statement when you run your view. The following is an example.
Running the View with Row Restriction
SELECT TOP 1000 [Category] ,[Subcategory] ,[Product] ,[TotQty] ,[TotSales] FROM [AdventureWorksDW2008R2].[dbo].[vwSalesByProduct2007] WHERE TotSales > 600000
You can apply a WHERE clause to your aggregate view just like you can to any other type of view.
You can create views for aggregation queries on some dimension. For example you could create a view for Sales by Product Category for each year.
You cannot pass variables into a view. If you need this functionality you can create a stored procedure.