Group By - XQuery 1.1

Group By Grammar

GroupByClause	::=	"group" "by" GroupingSpecList
GroupingSpecList	::=	GroupingSpec ("," GroupingSpec)*
GroupingSpec	::=	"$" VarName ("collation" URILiteral)?

The following description is part of the XQuery 1.1 W3C recommendation which is currently under development.

A FLWOR expression may optionally have a group by clause that operates on the tuple stream, replacing the original tuples by new tuples that represent groups. We will refer to the tuples before the action of the group by clause as pre-grouping tuples, and the tuples after the action of the group by clause as post-grouping tuples.

The post-grouping tuples have exactly the same variable-names as the pre-grouping tuples. The number of post-grouping tuples is less than or equal to the number of pre-grouping tuples. The group by clause assigns each pre-grouping tuple to a group, and generates one post-grouping tuple for each group. After the action of the group by clause, the pre-grouping tuples are no longer accessible. Subsequent clauses in the FLWOR expression operate on the post-grouping tuples, each of which represents a group.

Group By Examples

In the following section, we present some example queries in order to demonstrate the "group by" functionality. All of the queries are part of the XQuery 1.1 Use Cases document. The document is available at http://www.w3.org/TR/xquery-11-use-cases/#dataproducts. It also presents some example documents and the result of executing the queries below on these documents.

Example 1

declare variable $sales-records external;
<sales-qty-by-product>{
  for $sales in doc($sales-records)/*/record
  let $pname := $sales/product-name
  group by $pname
  order by $pname
  return
    <product name="{$pname}">{
      sum($sales/qty)
    }</product>
}</sales-qty-by-product>

Example 2

declare variable $sales-records external;
declare variable $stores external;
declare variable $products external;
<result>{ 
  for $sales in doc($sales-records)/*/record
  let $state := doc($stores)/*/store[store-number = $sales/store-number]/state
  let $category := doc($products)/*/product[name = $sales/product-name]/category
  group by $state, $category
  order by $state, $category
  return
    <group>
      {$state, $category}
      <total-qty>{sum($sales/qty)}</total-qty>
    </group>
}</result>

Example 3

declare variable $sales-records external;
declare variable $stores external;
declare variable $products external;
<result>{
  for $sales in doc($sales-records)/*/record
  let $state := doc($stores)/*/store[store-number = $sales/store-number]/state,
    $product := doc($products)/*/product[name = $sales/product-name],
    $category := $product/category,
    $revenue := $sales/qty * $product/price
  group by $state, $category
  order by $state, $category
  return
    <group>
      {$state, $category}
      <total-revenue>{sum($revenue)}</total-revenue>
    </group> 
}</result>

Example 4

declare variable $sales-records external;
declare variable $stores external;
declare variable $products external;
<result>{
  for $store in doc($stores)/*/store
  let $state := $store/state
  group by $state
  order by $state
  return
    <state name="{$state}">{
      for $product in doc($products)/*/product
      let $category := $product/category
      group by $category
      order by $category
      return
        <category name="{$category}">{
          for $sales in doc($sales-records)/*/record[store-number = $store/store-number
            and product-name = $product/name]
          let $pname := $sales/product-name
          group by $pname
          order by $pname
          return
            <product name="{$pname}" total-qty="{sum($sales/qty)}" />
          }</category>
    }</state> 
}</result>

Example 4

declare variable $sales-records external;
<result>{
  for $sales in doc($sales-records)/*/record
  let $storeno := $sales/store-number
  group by $storeno
  order by $storeno
  return
    <store number = "{$storeno}">{
      for $s in $sales
      order by xs:int($s/qty) descending
      return
        <product name = "{$s/product-name}" qty = "{$s/qty}"/>
    }</store> 
}</result>