Constraining and sorting on aggregated values : Aggregate « XQuery « XML

1. XML
2. XQuery
3. Aggregate

Constraining and sorting on aggregated values

File: Data.xml

<inventory id="inv0001" date="2008-12-19" loc="USA">
  <car model="A" num="0001" quantity="1" color="navy"/>
  <car model="B" num="0002" quantity="1" color="red"/>
  <car model="B" num="0003" quantity="2" color="red"/>
  <car model="C" num="0004" quantity="1" color="white"/>
  <car model="C" num="0004" quantity="1" color="gray"/>
  <car model="A" num="0001" quantity="1" color="black"/>
</inventory>



File: Query.xquery

let $allcars := doc("Data.xml")//car
for $d in distinct-values($allcars/@model)
for $n in distinct-values($allcars/@num)
let $cars := $allcars[@model = $d and @num = $n]
where sum($cars/@quantity) > 1
order by count($cars)
return if (exists($cars))
       then <group model="{$d}" num="{$n}" numcars="{count($cars)}"
                   totQuant="{sum($cars/@quantity)}"/>
       else ()

Output:

<?xml version="1.0" encoding="UTF-8"?>
<group totQuant="2" num="0003" model="B" numcars="1"/>
<group totQuant="2" num="0001" model="A" numcars="2"/>
<group totQuant="2" num="0004" model="C" numcars="2"/>

 

Related examples in the same category