Oracle has clause-go

Use the HAVING clause to select rows

The HAVING clause sets conditions on the GROUP by clause in a manner similar to how the WHERE clause interacts with the SELECT statement. The WHERE clause 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 that appears in the selection list.

The following query gets the current year-to-date sales of more than $40,000 publishers:

Use pubs

SELECT pub_id, total = SUM (ytd_sales)

From titles

GROUP by pub_id

Having SUM (ytd_sales) > 40000

Here is the result set:

pub_id Total

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

0877 44219

(1 row (s) affected)

To ensure that at least six books are included in the calculation for each publisher, the following example uses having COUNT (*) > 5 to eliminate the total number of returned publishers that are less than six books:

Use pubs

SELECT pub_id, total = SUM (ytd_sales)

From titles

GROUP by pub_id

Having COUNT (pub_id) > 5

Here is the result set:

pub_id Total

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

0877 44219

1389 24941

(2 row (s) affected)

Understanding the correct sequence of application WHERE, GROUP by, and having clauses can be useful for 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.

For search conditions that can be applied before or after a grouping operation, it is more efficient to specify them in the WHERE clause. 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™2000 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 instead of the HAVING clause.

The following query shows the HAVING clause that contains an aggregate function. This clause groups rows in the titles table by type and eliminates groups that contain only one book:

Use pubs

SELECT type

From titles

GROUP by Type

Having COUNT (type) > 1

Here is the result set:

Type

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

Business

Mod_cook

Popular_comp

Psychology

Trad_cook

(5 row (s) affected)

The following is an example of a HAVING clause without an aggregate function. This clause groups rows in the titles table by type, and eliminates those types that do not start with the letter p.

Use pubs

SELECT type

From titles

GROUP by Type

Having type = '%p% '

Here is the result set:

Type

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

Popular_comp

Psychology

(2 row (s) affected)

If there are multiple conditions in the having, the conditions are combined by and, or or not. The following example shows how to group titles by publisher, including only those publishers whose identification number is greater than 0800, the total advance payment has exceeded $15,000, and the average price of the sales book is less than $ $.

SELECT pub_id, SUM (advance) as amountadvanced,

AVG (price) as Averageprice

From Pubs.dbo.titles

WHERE pub_id > ' 0800 '

GROUP by pub_id

Having SUM (advance) > 15000

and AVG (price) < 20

Order by can be used to sort the output of the GROUP by clause. The following example shows the order by using an ORDER BY clause to define the return rows in a GROUP by clause:

SELECT pub_id, SUM (advance) as amountadvanced,

AVG (price) as Averageprice

From Pubs.dbo.titles

WHERE pub_id > ' 0800 '

and Price >= 5

GROUP by pub_id

Having SUM (advance) > 15000

and AVG (price) < 20

ORDER by pub_id DESC

Oracle has clause-go