XQuery

Aggregating Values

In addition to simply regrouping items, it is often desirable to perform calculations on the groups. For example, suppose you want to know the number of item elements in a department, or the sum of the quantities for a department. This type of aggregation can be performed using the aggregation functions. Example 7-10 shows some of these functions in action.

Example 7-10. Aggregation

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

Here is how the aggregation functions work:

count

This function is used to determine the number of items in the sequence. In Example 7-10, the count function is used to calculate the value of numItems, which is the number of items in the department. It is also used to calculate the value of distinctItemNums. In the latter case, the count function is combined with the distinct-values function to count only the unique numbers in that department.

sum

This function is used to determine the total value of the items in a sequence. In Example 7-10, the sum function is used to calculate the value ...