GROUP BY

The GROUP BY clause specifies the groups into which output rows are to be placed and, if aggregate functions are included in the SELECT or HAVING clauses, calculates the aggregate functions values for each group.

Arguments:

WITH ROLLUP

Specifies that in addition to the usual rows provided by GROUP BY, summary rows are introduced into the result set. Groups are summarized in a hierarchical order, from the lowest level in the group to the highest, and the corresponding summary rows contain NULL values for the groups that have been summarized.
The group hierarchy is determined by the order in which the grouping field-expressions are specified. Changing the order of the grouping field-expressions can affect the number of rows produced in the result set.
The ROLLUP operator is often used with the GROUPING aggregate function.

Remarks:

• When GROUP BY is specified, either each non-aggregate and non-constant field-expression in the SELECT clause should be included in the GROUP BY field-expression list, or the GROUP BY field-expression list must match exactly the SELECT clause field-expression list. For more information, see Aggregating Data Within Groups.
• Aggregate functions using the DISTINCT keyword, for example, "COUNT(DISTINCT field-expression)", are not supported when using the GROUP BY clause.
• If the ORDER BY clause is not specified, groups returned using the GROUP BY clause are not in any particular order. It is recommended that the ORDER BY clause is always used to specify a particular ordering of the data.

Examples:

A. Simple GROUP BY clause

The following query, on an IISW3C log file, returns the number of requests for each page on each day:

SELECT date, cs-uri-stem, COUNT(*)
FROM LogFiles\ex040528.log
GROUP BY date, cs-uri-stem

A sample output would be:

date       cs-uri-stem         COUNT(ALL *)
---------- ------------------- ------------
2003-11-18 /Default.htm        1
2003-11-18 /style.css          1
2003-11-18 /images/address.gif 1
2003-11-18 /cgi-bin/counts.exe 1
2003-11-18 /data/rulesinfo.nsf 2
2003-11-19 /data/rulesinfo.nsf 6
2003-11-20 /data/rulesinfo.nsf 5
2003-11-20 /maindefault.htm    1
2003-11-20 /top2.htm           1
2003-11-20 /homelog.swf        1

B. Using WITH ROLLUP

The following example query is the same as in the previous example, using the WITH ROLLUP argument to display additional summary rows:

SELECT date, cs-uri-stem, COUNT(*)
FROM LogFiles\ex040528.log
GROUP BY date, cs-uri-stem WITH ROLLUP

A sample output would be:

date       cs-uri-stem         COUNT(ALL *)
---------- ------------------- ------------
2003-11-18 /Default.htm        1
2003-11-18 /style.css          1
2003-11-18 /images/address.gif 1
2003-11-18 /cgi-bin/counts.exe 1
2003-11-18 /data/rulesinfo.nsf 2
2003-11-19 /data/rulesinfo.nsf 6
2003-11-20 /data/rulesinfo.nsf 5
2003-11-20 /maindefault.htm    1
2003-11-20 /top2.htm           1
2003-11-20 /homelog.swf        1
-          -                   20
2003-11-18 -                   6
2003-11-19 -                   6
2003-11-20 -                   8

The group summaries that have been introduced by the rollup operator are:

2003-11-18 -                   6
2003-11-19 -                   6
2003-11-20 -                   8
-          -                   20

Which represent the number of requests on each day, regardless of the page requested, and the total number of requests in the log file, regardless of the day.

See also:

Aggregating Data Within Groups

© 2004 Microsoft Corporation. All rights reserved.