Use having clause to select rows

The having clause sets conditions for the group by clause in a similar way as the WHERE clause and select statement. The where clause search condition is applied before grouping, while the having search condition is applied after grouping. Having syntax is similar to where syntax, but having can contain aggregate functions. Having clause can reference any item in the selection list.

The following query shows that the current sales volume of more than $40,000 million this year has been obtained:

Use pubs
Select pub_id, total = sum (ytd_sales)
From titles
Group by pub_id
Having sum (ytd_sales)> 40000

To ensure that each publisher's computation contains at least six books, the following example uses having count (*)> 5 to eliminate the number of publishers who return less than six books:

Use pubs
Select pub_id, total = sum (ytd_sales)
From titles
Group by pub_id
Having count (*)> 5

Understand the correct sequence of application where, group by, and having clauses for writing efficient queriesCodeIt will help:

The where clause is used to filter the rows generated 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 grouping results.
For search conditions that can be applied before or after grouping operations, it is more effective to specify them in the WHERE clause. This reduces the number of rows that must be grouped. The search conditions that should be specified in the having clause are only those that must be applied after the grouping operation is executed.

The following query displays the having clause containing Aggregate functions. This clause groups the rows in the titles table by type and removes the group containing only one book:

Use pubs
Select Type
From titles
Group by type
Having count (*)> 1

The following is an example of having clause without Aggregate functions. This clause groups rows in the titles table by type and removes the types not starting with the letter P.

Use pubs
Select Type
From titles
Group by type
Having type like 'P %'

If having contains multiple conditions, these conditions are combined by and, or, or not. The following example shows how to group titles by publisher, including only those publishers whose ID number is greater than 0800, whose total prepayment has exceeded $15,000, and whose average selling price is less than $20.

Select pub_id, sum (advance) as amountadvanced,
AVG (price) as averageprice
From pubs. DBO. Titles
Where pub_id> '123'
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 how to use the order by clause to define the order of rows in the return group by clause:

select pub_id, sum (advance) as amountadvanced,
AVG (price) as averageprice
from pubs. DBO. titles
where pub_id> '000000'
and price >=$ 5
group by pub_id
having sum (advance)> $15000
and AVG (price) <$20
order by pub_id DESC