xquery-3.0

XQuery - Sum attributes for all element which have another specific attribute value

I need to add balance for each prod "type" such that what remains is one element with the total balance for the prod "type"

This is what I have

let $input := <products>
                <prod balance="20000" code="car"/>
                <prod balance="50000" code="house"/>
                <prod balance="50000" code="car"/>
              </products>         
       let $sum_val_car :=  sum($input//prod/@balance )
       let $count_val_car := count($input//prod[@code='car'])
       let $sum_val_house :=  sum($input//prod/@balance)
       let $count_val_house := count($input//prod[@code='house'])
       
       
  return  <products>
            <prod count="{$count_val_car}" balance="{$sum_val_car}" code="car"/>
            <prod count="{$count_val_house}" balance="{$sum_val_house}" code="house"/>
         </products>

I need to modify $sum_val_car and $sum_val_house somehow to only sum the elements with the code attribute eqaul to the repective value. Is there a way to do this?

Solution 
  • $input//prod[@code='house']/@balance

    This chooses balance from prod elements with code='house'

    Solution is then

    let $sum_val_house :=  sum($input//prod[@code='house']/@balance)