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]


aggview

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


aggview2

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.



 Collapse All    Expand All
Custom Search

SQL Server Information and Resources