group by and having Clauses

Used in select statements to divide a table into groups and to return only groups that match conditions in the having clause. group by is typically used with aggregates to specify how to group the unaggregated columns of a select query. having clauses are applied to these groups.

Syntax

Start of select statement

[group by [all] aggregate_free_expression
	[, aggregate_free_expression]...]

[having search_conditions]

End of select statement

Parameters

group by – specifies the groups into which the table is divided, and if aggregate functions are included in the select list, finds a summary value for each group. These summary values appear as columns in the results, one for each group. You can refer to these summary columns in the having clause.
You can use the avg, count, count_big, max, min, and sum aggregate functions in the select list before group by (the expression is usually a column name). See Reference Manual: Building Blocks.

A table can be grouped by any combination of columns—that is, groups can be nested within each other, as in Example 2.

all – is a Transact-SQL extension that includes all groups in the results, even those excluded by a where clause. For example:

select type, avg (price) 
from titles 
where advance > 7000 
group by all type

type 
-----------------  ---------- 
UNDECIDED                NULL
business                 2.99
mod_cook                 2.99
popular_comp            20.00
psychology               NULL
trad_cook               14.99

 (6 rows affected)

“NULL” in the aggregate column indicates groups that would be excluded by the where clause. A having clause negates the meaning of all.

aggregate_free_expression – is an expression that includes no aggregates. A Transact-SQL extension allows grouping by an aggregate-free expression as well as by a column name.
You cannot group by column heading or alias. This example is correct:
```
select Price=avg (price), Pay=avg (advance), 
Total=price * $1.15 
from titles 
group by price * $1.15
```
having – sets conditions for the group by clause, similar to the way in which where sets conditions for the select clause.
having search conditions can include aggregate expressions; otherwise, having search conditions are identical to where search conditions. This is an example of a having clause with aggregates:
```
select pub_id, total = sum (total_sales) 
from titles 
where total_sales is not null 
group by pub_id 
having count (*)>5
```
When the SAP ASE server optimizes queries, it evaluates the search conditions in where and having clauses, and determines which conditions are search arguments (SARGs) that can be used to choose the best indexes and query plan. All of the search conditions are used to qualify the rows. For more information on search arguments, see the Performance and Tuning Guide: Optimizer and Abstract Plans.

Examples

Example 1 – Calculates the average advance and the sum of the sales for each type of book:
```
select type, avg (advance), sum (total_sales) 
from titles 
group by type
```

Example 2 – Groups the results by type, then by pub_id within each type:

select type, pub_id, avg (advance), sum (total_sales) 
from titles 
group by type, pub_id

Example 3 – Calculates results for all groups, but displays only groups whose type begins with “p”:
```
select type, avg (price) 
from titles 
group by type 
having type like 'p%'
```

Example 4 – Calculates results for all groups, but displays results for groups matching the multiple conditions in the having clause:

select pub_id, sum (advance), avg (price) 
from titles 
group by pub_id 
having sum (advance) > $15000 
and avg (price) < $10 
and pub_id > "0700"

Example 5 – Calculates the total sales for each group (publisher) after joining the titles and publishers tables:
```
select p.pub_id, sum (t.total_sales)
from publishers p, titles t
where p.pub_id = t.pub_id
group by p.pub_id
```
Example 6 – Displays the titles that have an advance of more than $1000 and a price that is more than the average price of all titles:
```
select title_id, advance, price
from titles
where advance > 1000
having price > avg (price)
```

Usage

You can use a column name or any expression (except a column heading or alias) after group by. You can use group by to calculate results or display a column or an expression that does not appear in the select list).
The maximum number of group by columns (or expressions) is not explicitly limited. The only limit of group by results is that the width of the group by columns plus the aggregate results be no greater than 64K.
Null values in the group by column are put into a single group.
You cannot name text, unitext, or image columns in group by and having clauses.
You cannot use a group by clause in the select statement of an updatable cursor.
Aggregate functions can be used only in the select list or in a having clause. They cannot be used in a where or group by clause.

Aggregate functions are of two types. Aggregates applied to all the qualifying rows in a table (producing a single value for the whole table per function) are called scalar aggregates. An aggregate function in the select list with no group by clause applies to the whole table; it is one example of a scalar aggregate.

Aggregates applied to a group of rows in a specified column or expression (producing a value for each group per function) are called vector aggregates. For either aggregate type, the results of the aggregate operations are shown as new columns that the having clause can refer to.

You can nest a vector aggregate inside a scalar aggregate. See Reference Manual: Building Blocks for more information.

See also Transact-SQL Functions in Reference Manual: Building Blocks.

Standards

ANSI SQL – Compliance level: Entry-level compliant.

The use of columns within the select list that are not in the group by list and have no aggregate functions is a Transact-SQL extension.

The use of the all keyword is a Transact-SQL extension.