Aggregation on multiple values
File: Data.xml
<employees>
<employee name="James" salary="1234" yearServed="1" location="North"/>
<employee name="Joe" salary="3211" yearServed="2" location="South"/>
<employee name="Jod" salary="3422" yearServed="3" location="North"/>
<employee name="Jason" salary="1321" yearServed="4" location="East"/>
<employee name="Jack" salary="2324" yearServed="3" location="North"/>
<employee name="Jeep" salary="4321" yearServed="2" location="East"/>
<employee name="Jane" salary="2344" yearServed="1" location="South"/>
</employees>
File: Query.xquery
let $allemployees := doc("Data.xml")//employee
for $d in distinct-values($allemployees/@salary)
for $n in distinct-values($allemployees[@salary = $d]/@yearServed)
let $employees := $allemployees[@salary = $d and @yearServed = $n]
order by $d, $n
return <group salary="{$d}"
yearServed="{$n}"
numemployees="{count($employees)}"
total="{sum($employees/@yearServed)}"/>
Output:
<?xml version="1.0" encoding="UTF-8"?>
<group total="1" yearServed="1" salary="1234" numemployees="1"/>
<group total="4" yearServed="4" salary="1321" numemployees="1"/>
<group total="3" yearServed="3" salary="2324" numemployees="1"/>
<group total="1" yearServed="1" salary="2344" numemployees="1"/>
<group total="2" yearServed="2" salary="3211" numemployees="1"/>
<group total="3" yearServed="3" salary="3422" numemployees="1"/>
<group total="2" yearServed="2" salary="4321" numemployees="1"/>
Related examples in the same category