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

Question

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

Evan Lenz · Accepted Answer · 2011-12-22T21:12:19.613

12

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.

edited Dec 22 '11 at 21:12

answered Dec 22 '11 at 18:34

Evan Lenz

4,086
1
19
18

one more question, how would you organise the results according to the number of instances? – ritzdiamond Dec 22 '11 at 19:51
You'd use an "order by" clause. See the example, now updated. – Evan Lenz Dec 22 '11 at 21:13

score 6 · Answer 2 · answered Dec 23 '11 at 02:50

This is just an XPath 2,0 expression that seems shorter:

for $v in distinct-values(/*/*/@color)
 return
    ($v, count(index-of(/*/*/@color, $v)), '&#xA;')

When this XPath expression is evaluated against the provided XML document:

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

the wanted, correct result is produced:

blue 2 
red 1

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

2 Answers2