How to count each instance of distinct-values in XQuery?

ritzdiamond picture ritzdiamond · Dec 22, 2011 · Viewed 10.8k times · Source

I have an XML file with the following elements and attributes:

<element>
<unit id="1" color="blue"/>
<unit id="2" color="blue"/>
<unit id="3" color="red"/>
</element>

How do I get the count of 1) distinct occurrences of each attribute color and 2) count for each distinct occurrence?

So far, I've tried both distinct-values() and count() and their combinations, with no luck, ending with either the number of distinct attributes or the number of them, although I'd like to get both.

The list of results that I'd like to get would look like this:

Blue 2
Red 1

Answer

Evan Lenz picture Evan Lenz · Dec 22, 2011

This query should do what you want:

let $input :=
  <element>
    <unit id="1" color="blue"/>
    <unit id="2" color="blue"/>
    <unit id="3" color="red"/>
  </element>

return
  for $value in distinct-values($input/unit/@color)
  let $count := count($input/unit[@color eq $value])
  order by $count descending
  return concat($value," ",$count)

This produces the following output for me in MarkLogic:

blue 2
red 1

UPDATE: I modified the query so it orders the result by the count value.