SQL having usage explained

From: http://blog.csdn.net/wozeze1/article/details/6031318

The HAVING clause sets conditions on the GROUP by clause in a manner similar to WHERE and SELECT. The Where search condition is applied before the grouping operation, while the having search condition is applied after the grouping operation. The having syntax is similar to the WHERE syntax, but the having can contain aggregate functions. The HAVING clause can refer to any item displayed in the selection list.

The following example groups SalesOrderDetail by Product ID and contains only those product groups whose order totals are greater than $1,000,000 and their average order quantity is less than 3.

Use AdventureWorks;

GO

SELECT ProductID, AVG (OrderQty) as Averagequantity, SUM (LineTotal) as Total

From Sales.SalesOrderDetail

GROUP by ProductID

Having SUM (LineTotal) > $1000000.00

and AVG (OrderQty) < 3;

GO

Note that if there are multiple conditions in the having, then these conditions will be combined by and, or or not.

To view products with a total sales volume greater than $2,000,000, use the following query:

Use AdventureWorks;

GO

SELECT ProductID, total = SUM (LineTotal)

From Sales.SalesOrderDetail

GROUP by ProductID

Having SUM (LineTotal) > $2000000.00;

GO

Here is the result set:

ProductID Total

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

781 3864606.54937208

969 2010943.97244001

793 2897478.01200001

784 3699803.72383008

780 3880441.60780208

976 2079038.42948

795 2268057.09000002

783 4548164.01783709

779 4170215.3849281

782 5032968.13026809

794 2679200.01336002

753 2006264.4236

(s) affected)

To ensure that at least 1500 items are included in the calculation for each product, use having COUNT (*) > 1500 to eliminate the products that return a total of less than 1500 items of sales. The query resembles the following example:

Use AdventureWorks;

GO

SELECT ProductID, SUM (LineTotal) as Total

From Sales.SalesOrderDetail

GROUP by ProductID

Having COUNT (*) > 1500;

GO

Understanding the correct order of application WHERE, GROUP by, and having clauses can be helpful in writing efficient query code:

The WHERE clause is used to filter the rows produced by the operation specified in the FROM clause.

The GROUP BY clause is used to group the output of the WHERE clause.

The HAVING clause is used to filter rows from the results of a grouping.

Specifying them in the WHERE clause is more efficient for any search criteria that can be applied before or after a grouping operation. This can reduce the number of rows that must be grouped. The search criteria that should be specified in the HAVING clause are only those that must be applied after the grouping operation.

The Microsoft SQL Server 2005 query optimizer can handle most of these conditions. If the query optimizer determines that a having search condition can be applied before the grouping operation, it is applied before grouping. The query optimizer may not recognize all the having search conditions that can be applied before the grouping operation. It is recommended that all of these search conditions be placed in the WHERE clause, not in the HAVING clause.

The following example shows a HAVING clause with an aggregate function. It groups the rows in the SalesOrderDetail table by product ID and eliminates the product whose average order quantity is less than/equal to 5.

Use AdventureWorks;

GO

SELECT ProductID

From Sales.SalesOrderDetail

GROUP by ProductID

Having AVG (OrderQty) > 5

ORDER by ProductID;

GO

The following example shows a HAVING clause without an aggregate function. It groups the rows in the ProductModel table by name and eliminates those names that do not start with Mountain.

Use AdventureWorks;

GO

SELECT pm. Name, AVG (ListPrice) as ' Average List price '

From Production.Product as P

JOIN Production.ProductModel as PM

On p.productmodelid = pm. ProductModelID

GROUP by PM. Name

Having PM. Name like ' mountain% '

ORDER by PM. Name;

GO

Note that the ORDER BY clause can be used to sort the output of the GROUP by clause.

SQL having usage explained