count element using Xquery and return the value

Question

my xml data:

<a>
   <book>
        <pub>John</pub>
   </book>
   <book>
        <pub>John</pub>
    </book>
    <book>
         <pub>Mary</pub>
    </book>
</a>

So i want to count number for each and display them

Expected output:

 <result>
         <pub>John</pub>
         <total>2</total>
 </result>
 <result>
         <pub>Mary</pub>
         <total>1</total>
  </result>

But my output:

 <result>
         <pub>John</pub>
         <total>1</total>
 </result>
<result>
         <pub>John</pub>
         <total>1</total>
 </result>
 <result>
         <pub>Mary</pub>
         <total>1</total>
  </result>

code i using :

for $b in /a/book
let $count := count($b/pub)
for $pub in $b/pub
return
     <result> {$pub} <total>{$count}</total></result>

it keep looping the same data but not group it . even i use distinct-values it's still same. what error in my code?

Answer 1

If using an XQuery 3.0 capable XQuery processor, you can also take advantage of the group by flwor statement:

for $book in /a/book
let $publisher := $book/pub
group by $publisher
return
  <result>
    <pub>{ $publisher }</pub>
    <count>{ count($book) }</count>
   </result>