SQL Server - HAVING

HAVING Clause

The HAVING clause is used in conjunction with GROUP BY. You can think of HAVING as a WHERE clause that is specific to data you are aggregating. HAVING can be used in concert with a WHERE clause.

A WHERE clause provides row restriction, whereas the HAVING clause is applied specifically to the grouped recordset that has been returned. The following is an example of a query containing a HAVING clause.

USE AdventureWorksDW2008R2
GO

SELECT     
 DimProductCategory.EnglishProductCategoryName AS "Category",
 DimProductSubcategory.EnglishProductSubcategoryName AS "Subcategory",
 DimProduct.EnglishProductName AS "Product",
 SUM(FactInternetSales.OrderQuantity) AS "Tot Qty", 
  SUM(FactInternetSales.SalesAmount) AS "Tot Sales"
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
  AND DimProductCategory.EnglishProductCategoryName = 'Bikes'
GROUP BY DimProductCategory.EnglishProductCategoryName, 
         DimProductSubcategory.EnglishProductSubcategoryName, 
         DimProduct.EnglishProductName 
HAVING   SUM(FactInternetSales.OrderQuantity) < 100
   AND   SUM(FactInternetSales.SalesAmount) < 10000       
ORDER BY DimProductCategory.EnglishProductCategoryName ASC,
         DimProductSubcategory.EnglishProductSubcategoryName ASC,
         DimProduct.EnglishProductName ASC


having

 Collapse All    Expand All
Custom Search

SQL Server Information and Resources