XQuery - group by and count

Question

This is the structure of my XML file -

<A>
  <Name t="Mr.">James Bond</Name>
  <Name t="Mr.">Allen Bond</Name>
  <Name t="Mr." p="X">James Bond</Name>
  <Name t="Mr." p="X">James Bond</Name>
  <Name t="Mr.">James Bond</Name>
  <Name t="Mrs.">James Bond</Name>
  <Name t="Mrs.">James Bond</Name>
  <Name t="Mr.">James Bond</Name>
  <Name t="Mrs." p="Y">James Bond</Name>
  <Name t="Mrs." p="Y">James Bond</Name>
</A>

The output I am expecting is -

<A>
  <N>Allen Bond Mr - 1</N>
  <N>James Bond Mr - 3</N>
  <N>James Bond Mr, X - 2</N>
  <N>James Bond Mrs - 2</N>  
  <N>James Bond Mrs, Y - 2</N>
</A>

I am able to get the distinct names but cant add the count...

possible duplicate of [Counting values obtained using the substring function in XQuery](http://stackoverflow.com/questions/13567209/counting-values-obtained-using-the-substring-function-in-xquery) — Mohammed Azharuddin Shaikh, Dec 26 '12 at 05:27

score 10 · Accepted Answer · answered Dec 25 '12 at 12:02

XQuery 3.0 solution with group by:

<A>{
  for $name in //Name
  let $full :=
    if(not($name/@p)) then concat($name, ' ', $name/@t)
    else concat($name, ' ', $name/@t, ', ', $name/@p)
  group by $full
  return <N>{$full, '-', count($name)}</N>
}</A>

XQuery 1.0 solution with distinct-values($arg):

<A>{
  let $names :=
    for $name in //Name
    return if(not($name/@p)) then concat($name, ' ', $name/@t)
    else concat($name, ' ', $name/@t, ', ', $name/@p)
  for $name in distinct-values($names)
  return <N>{$name, '-', count($names[. eq $name])}</N>
}</A>

XQuery - group by and count

1 Answers1

Linked

Related