xquery sum and multiply

Question

I need to multiply several pairs of values and sum them up, but all i get is a row of the numbers. (E.g: The numbers which i need to sum up are 12 and 13, what i need is 25, what i get is "1213") I already tried different kinds of typecasting and tried to build workarounds, but i can't get to a solution. (the value of the "$x" variable is the problem)

Here is my code:

let $hk := doc('http://etutor.dke.uni-linz.ac.at/etutor/XML?id=1')/handelskette
for $n in distinct-values($hk/kunden/kunde/bonStufe)

let $k := $hk/kunden/kunde[bonStufe=$n]
let $c := count($k)
let $r := $hk/rechnungen/rechnung[kundeNr=$k/@kundeNr]/rposition
let $x := 0

order by $n

return <bStufe val="{$n}">
<umsGesamt>{
  for $i in $r
  let $x := sum(xs:integer($i/einzelPreis) * xs:integer($i/menge))
  return $x
}</umsGesamt>
<umsProKunde>{(:$x div :)$c}</umsProKunde>
</bStufe>

$i looks like (i would need sum(einzelPreis*menge)):

<rposition>
  <ean>5-6661-000-0-00</ean>
  <einzelPreis>500</einzelPreis>
  <menge>3</menge>
</rposition>

My result looks like this:

<bStufe val="A">
  <umsGesamt>150015752450140004507606001100</umsGesamt>
  <umsProKunde>5</umsProKunde>
</bStufe>
<bStufe val="B">
  <umsGesamt>2292250068006602200690070001200045001530</umsGesamt>
  <umsProKunde>4</umsProKunde>
</bStufe>
<bStufe val="C">
  <umsGesamt>29010001590130013502600150195012900320010003751000</umsGesamt>
  <umsProKunde>4</umsProKunde>
</bStufe>

I would really appreciate some help.

Best Regards Lucas

Please, never use single-digit variables. They make understanding the code unnecessarily complicated and writing it error-prone. What about `$count` instead of `$c`? Same applies to pretty much all the variable names you used. — Jens Erat, May 01 '14 at 20:02
When i have to write more lines of code i usually do this. The only important variables here are $i (which represents the rposition node, described above) and $x which should calculate the income which should be the sum of all ($i/einzelPreis * $i/menge) (in english: unitprice * amount) — Lucas, May 01 '14 at 20:13
Don't do it sometimes, do it always. If you're asking somebody for help, somebody has to read the code. I guess this is an assignment, if you have to hand it in: somebody has to read the code. Those short variable names don't save you any time at all, and the times when programmers had to care about bytes and printed characters are long, long gone. — Jens Erat, May 01 '14 at 20:32

score 1 · Accepted Answer · edited May 23 '17 at 12:11

I did not completely get what you're after, but I'm pretty sure anyway what the problem is. To me it seems, that you want to calculate the sum of all products. Instead, you're returning the set of sums of individual products. Instead of

<umsGesamt>{
  for $i in $r
  let $x := sum(xs:integer($i/einzelPreis) * xs:integer($i/menge))
  return $x
}</umsGesamt>

try

<umsGesamt>{
  sum(
    for $i in $r
    let $x := $i/einzelPreis * $i/menge
    return $x
  )
}</umsGesamt>

or the shorter XQuery 3.0 version using functions in axis steps

<umsGesamt>{sum($r/(einzelPreis * menge))}</umsGesamt>

Regarding the line let $x := 0, read up again on what the differences between procedural and functional programming are. An example can also be found in this answer: Updating counter in XQuery.

xquery sum and multiply

1 Answers1