Microsoft® SQL Server® 2012 Bible

256

Part II: Building Databases and Working with Data

properly fi lter before or after the GROUP BY. SQL, however, uses the HAVING clause to fi l-

ter the groups. At the beginning of this chapter, you saw the simplifi ed order of the SQL

SELECT statement’s execution. A more complete order is as follows:

1. The FROM clause assembles the data from the data sources.


2. The WHERE clause restricts the rows based on the conditions.


3. The GROUP BY clause assembles subsets of data.


4. Aggregate functions are calculated.


5. The HAVING clause fi lters the subsets of data.


6. Any remaining expressions are calculated.


7. The ORDER BY sorts the results.
   The following query removes from the analysis any grouping having a sum of greater than
   or equal to 10 million:

SELECT

st.CountryRegionCode,

SUM(TotalDue) TotalSalesAmount

FROM Sales.SalesOrderHeader soh

INNER JOIN Sales.SalesTerritory st

ON soh.TerritoryID = st.TerritoryID

GROUP BY

st.CountryRegionCode

HAVING SUM(TotalDue) > 10000000

Result :

CountryRegionCode TotalSalesAmount

----------------- ---------------------

AU 11814376.0952

CA 18398929.188

US 70829863.203

Without the HAVING clause, all Countries or Regions will be included in the results.

Windowing and Ranking .....................................................................................

T-SQLs windowing and ranking function provides developers with the ability to shed new

light on the same old data. Windowing as it relates to SQL Server is not related in any

way to Microsoft Windows. Instead, it refers to ways of working with subsets of data.

Windowing, using the OVER() clause, provides a new perspective of the data. The ranking

functions use the new perspective to provide additional ways for manipulating the data.

c10.indd 256c10.indd 256 7/30/2012 4:26:13 PM7/30/2012 4:26:13 PM

http://www.it-ebooks.info