How can I add in a for loop in Xquery?

Question

I have a problem with an exercise in Xquery.

This is the exercise:

Get what every teacher would gain per month (the sum of all) whose name begins with Michael if all the places of his courses were completed.

And this is the xml file:

<shop>
<training>
  <course id="1">
      <name>Java</name>
      <price fee="Monthly">27</price>
      <places>20</places>
      <teacher>Michael James</teacher>
  </course>
  <course id="2">
      <name>Android</name>
      <price fee="Monthly">47</price>
      <places>15</places>
      <teacher>Michael Pit</teacher>
  </course>
  <course id="3">
      <name>SEO</name>
      <price fee="Monthly">37</price>
      <places>55</places>
      <teacher>Michael Smith</teacher>
  </course>
  <course id="4">
      <name>HTML</name>
      <price fee="Monthly">99</price>
      <places>10</places>
      <teacher>Michael Kit</teacher>
  </course>
  <course id="5">
      <name>CSS</name>
      <price fee="Monthly">749</price>
      <places>5</places>
      <teacher>George Pet</teacher>
  </course>

I am trying to do this:

` for $x in doc("LM")//course[starts-with(teacher, "Michael")]
let $monthly-profits-by-course := $y/places * $y/price
let $total-profits := sum($monthly-profits-by-course) 
return 
<courses>
    <michael_profits>{$total-profits}</michael_profits>
</courses>`

This is the result:

<courses>
<michael_profits>540</michael_profits>
</courses>
<courses>
<michael_profits>705</michael_profits>
</courses>
<courses>
<michael_profits>2035</michael_profits>
</courses>
<courses>
<michael_profits>990</michael_profits>
</courses>

It lists the monthly profits by course, but I need the total profits and I don´t know how can I do that. I have tried it using only "let" instead of "for", but this doesn´t allow me to multiply places by price, I don´t know why. Someone could I help me, please? Thanks you very much.

Please include what code you have tried already. Also, please follow the other advice in stackoverflow.com/help/how-to-ask. — BPS
– BPS, Commented May 4, 2017 at 15:54

dirkk · Accepted Answer · 2017-05-04 14:45:27Z

Your $monthly-profits-by-course will always be a single value, not a sequence as you iterate over each course. Hence, sum($monthly-profits-by-course) will be equal to $monthly-profits-by-course itself. What you want is to return a sequence of the profits by each teacher like you already did:

for $x in doc("LM")//course[starts-with(teacher, "Michael")]
return $y/places * $y/price

And then calculate the sum of all this values. Combined, this would look like:

let $all-sums :=
  for $x in doc("LM")//course[starts-with(teacher, "Michael")]
  return $y/places * $y/price
return sum($all-sums)

And you can shorten this simply to:

sum(
  for $x in doc("LM")//course[starts-with(teacher, "Michael")]
  return $y/places * $y/price
)

And if your XQuery precessor supports XQuery 3.0 you could use the map ! operator and write:

sum(doc("LM")//course[starts-with(teacher, "Michael")] ! (./places * ./price))

Collectives™ on Stack Overflow

How can I add in a for loop in Xquery?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related