XQuery

Aggregating and Grouping Values

One common use for XQuery is to summarize and group XML data. It is sometimes useful to find the sum, average, or maximum of a sequence of values, grouped by a particular value. For example, suppose you want to know the number of items contained in an order, grouped by department. The query shown in Example 1-12 accomplishes this. It uses a for clause to iterate over the list of distinct departments, a let clause to bind $items to the item elements for a particular department, and the sum function to calculate the totals of the quantity attribute values for the items in $items.

Example 1-12. Aggregating values

Query
for $d in distinct-values(doc("order.xml")//item/@dept)
let $items := doc("order.xml")//item[@dept = $d]
order by $d
return <department name="{$d}" totQuantity="{sum($items/@quantity)}"/>
Results
<department name="ACC" totQuantity="3"/>
<department name="MEN" totQuantity="2"/>
<department name="WMN" totQuantity="2"/>

Chapter 7 covers joining, sorting, grouping, and aggregating values in detail.