Transact-SQL User's Guide

Home

Organizing query results into groups: the group
by clause

Referencing other columns in queries using group
by

Transact-SQL User's Guide

Chapter 3 Using Aggregates, Grouping, and Sorting

Organizing query results into groups: the group by clause

group by syntax

The complete syntax of the select statement is repeated here so that you can see the group by clause in context:

select [all | distinct] select_list 
     [into [[database.]owner.]table_name] 
     [from [[database.]owner.]{view_name | table_name          [(index {index_name | table_name} 
               [parallel [degree_of_parallelism]]
               [prefetch size ] [lru | mru])]}
          [holdlock | noholdlock] [shared]
     [,[[database.]owner.]{view_name | table_name          [(index {index_name | table_name} 
               [parallel [degree_of_parallelism]]
               [prefetch size] [lru | mru])]}
          [holdlock | noholdlock] [shared]]...] 
 
     [where search_conditions] 
 
     [group by [all] aggregate_free_expression          [, aggregate_free_expression]...]
     [having search_conditions]
 
     [order by 
     {[[[database.]owner.] {table_name. | view_name.}]
          column_name | select_list_number | expression} [asc | desc]
     [, {[[[database.]owner.]{table_name|view_name.}]          column_name | select_list_number | expression} 
               [asc | desc]]...]
 
     [compute row_aggregate (column_name)
               [, row_aggregate(column_name)]...
          [by column_name [, column_name]...]] 
 
     [for {read only | update [of column_name_list]}]
 
     [at isolation {read uncommitted | read committed | serializable}]
 
     [for browse]

Remember that the order of the clauses in the select statement is significant. You can omit any of the optional clauses, but when you use them, they must appear in the order shown above.

group by and SQL standards

The SQL standards for group by are more restrictive than Sybase's standard. The SQL standard requires that:

The columns in a select list must be in the group by expression or they must be arguments of aggregate functions.
A group by expression can only contain column names in the select list, but not those used only as arguments for vector aggregates.

Several Transact-SQL extensions (described in the following sections) relax these restrictions, however the more complex result sets may be more difficult to understand. If you set the fipsflagger option as follows, you will receive a warning message stating that Transact-SQL extensions are used:

set fipsflagger on

For more information about the fipsflagger option, see the set command in the Reference Manual.

Nesting groups with group by

You can list more than one column in the group by clause to nest groups. Once the sets are established with group by, the aggregates are applied. This statement finds the average price and the sum of book sales, grouped first by publisher identification number and then by type:

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

pub_id  type
------  ------------  ------  ------- 
0736    business       2.99  18,722 
0736    psychology    11.48   9,564 
0877    UNDECIDED      NULL    NULL 
0877    mod_cook      11.49  24,278 
0877    psychology    21.59     375 
0877    trad_cook     15.96  19,566 
1389    business      17.31  12,066 
1389    popular_comp  21.48  12,875 
 
(8 rows affected)

You can nest many groups within groups, up to the maximum of 16 columns or expressions specified with group by.


	Expand Search