Mega Code Archive

 

Categories / XML / Xquery

 

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"/>