How do i sum net amount based on policyno in xml tag by using Xquery

Question

output should like :

PolicyNo NetAmount POL-01 300 POL-02 500

below query gives me Total of both policy instead of group wise Please guide me for the same.

Below is my xml :

<StatementDetail>  
        <PolicyNo>POL-01</PolicyNo>   
        <NetAmount>250.00</NetAmount>              
</StatementDetail> 
<StatementDetail>  
        <PolicyNo>POL-01</PolicyNo>   
        <NetAmount>50.00</NetAmount>              
</StatementDetail> 
<StatementDetail>  
        <PolicyNo>POL-02</PolicyNo>   
        <NetAmount>270.00</NetAmount>              
</StatementDetail>  
<StatementDetail>  
        <PolicyNo>POL-02</PolicyNo>   
        <NetAmount>230.00</NetAmount>              
</StatementDetail> 

and my xquery is :

#xquery(let $sas := StatementDetail
let $sum :=sum($sas/NetAmount[. castable as xs:double])
return <StatementDetail><NetAmount>{$sum}</NetAmount></StatementDetail>)

Answer 1

I implemented it by using BaseX v.9.4.1

The solution needs to use group by clause to get a proper sum calculation.

XQuery

declare context item := document {
<root>
<StatementDetail>  
        <PolicyNo>POL-01</PolicyNo>   
        <NetAmount>250.00</NetAmount>              
</StatementDetail> 
<StatementDetail>  
        <PolicyNo>POL-01</PolicyNo>   
        <NetAmount>50.00</NetAmount>              
</StatementDetail> 
<StatementDetail>  
        <PolicyNo>POL-02</PolicyNo>   
        <NetAmount>270.00</NetAmount>              
</StatementDetail>  
<StatementDetail>  
        <PolicyNo>POL-02</PolicyNo>   
        <NetAmount>230.00</NetAmount>              
</StatementDetail> 
</root>
};

<root>
{
  for $x in ./root/StatementDetail
  let $PolicyNo := fn:upper-case($x/PolicyNo)
  group by $PolicyNo
  order by $PolicyNo
  return <statement>
        <StatementDetail>{$PolicyNo}</StatementDetail>
        <NetAmountSum>{sum($x/NetAmount)}</NetAmountSum>
      </statement>
}
</root>

Output

<root>
  <statement>
    <StatementDetail>POL-01</StatementDetail>
    <NetAmountSum>300</NetAmountSum>
  </statement>
  <statement>
    <StatementDetail>POL-02</StatementDetail>
    <NetAmountSum>500</NetAmountSum>
  </statement>
</root>