countxquerydistinct-valuesbasex

XQUERY Counting & Unique Values

Working in BaseX, I'm trying to list the number of publishers in an xml database that contains a bookstore and how many books there are for each publisher. So I have the below xquery:

let $publisher:= distinct-values(doc("bookstore.xml")/bookstore/book/publisher)

  for $value in ($publisher)
  let $count := count($publisher= $value)
  order by $value ascending
  return concat($value," ",$count)

And this is returning just a list of the publishers and 1:

Publisher A 1

Publisher B 1

Publisher C 1

And so on. Assuming I have in my xml several books for each piblisher, I am not finding the way to show this.

let $publisher:= distinct-values(doc("bookstore.xml")/bookstore/book/publisher)

  for $value in ($publisher)
  let $count := count($publisher= $value)
  order by $value ascending

Results:

Publisher A 1

Publisher B 1

Publisher C 1
Solution

  • Firstly, since $publisher only contains the distinct publishers, comparing $publisher to $value makes little sense. And then, the result of $publisher= $value is a boolean, and count() applied to a boolean is always going to return 1.

    Try

    let $all-publishers:= doc("bookstore.xml")/bookstore/book/publisher
let $distinct-publishers := distinct-values($all-publishers)
for $value in $distinct-publishers
let $count := count($all-publishers[. = $value])

    But it's probably better to handle this using group by.