XQuery aggregate result AND give total

Question

I have an XML inventory of items.

The inventory lists how much if Item with ID 1 I have, how much of Item with ID 2, etc (think each item ID represents 1 product). The list, however, is subdivided, depending on what quantity of items of particular type have a particular mark on them. So, I have let us say:

Item ID  Marked? Qty
1        (no)     500
1         ABC     100
1         (no)     50
1         FFFF    333
2         (no)  10000

....

This ir represented in a structure like this:

<Base>
<Item>
   <Id>1</Id>
   <Qty>500</Qty>
</Item>
<Item>
   <Id>1</Id>
   <Qty>100</Qty>
   <Mark>ABC</Mark>
</Item>
<Item>
   <Id>1</Id>
   <Qty>50</Qty>
</Item>
<Item>
   <Id>1</Id>
   <Qty>333</Qty>
   <Mark>FFFF</Mark>
</Item>
<Item>
   <Id>2</Id>
   <Qty>10000</Qty>
</Item>
...
</Base>

Using XQuery transformation I wish to produce, instead of multiple rows per item ID, 1 aggregate row for each item ID, which would list both total sum of quantities per item (both marked and unmarked), and a separate sum for only those quantities of item that are marked (have subtag. I am not concerned about the contents of the individual tags).

In table form what I want would be this:

    Item ID  Marked Qty  Total Qty
    1          433          983
    2            0        10000
etc.

In actual XML form the transformation should produce something like this:

<Item><Id>1</Id><MarkedQuantity>433</MarkedQuantity><Total>983</Total></Item>
<Item><Id>2</Id><Total>10000</Total></Item>
....

UPD: Edited for clarity.

You may or may not be overcomplicating it, but the question is complicated enough that this reader has no idea what you're asking. Downvoting question as too complicated to be understood by others and unlikely for that reason to be useful to them. — C. M. Sperberg-McQueen, Oct 16 '17 at 18:40
@C.M.Sperberg-McQueen Thank you. I added clarification. Hopefully this will be more useful. — Gnudiff, Oct 17 '17 at 11:52

Martin Honnen · Answer 1 · 2017-10-16T09:28:39.573

If you use

for $item in Base/Item
group by $id := $item/Id
order by $id
return <Item>
  <Id>{$id}</Id>
  {for $marked in $item[Mark]
    group by $mark  := $marked/Mark
    return <Marked name="{$mark}">
      {sum($marked/Qty)}
      </Marked>
      }
  <Total>{sum($item/Qty)}</Total>
</Item>

you will get

<Item>
   <Id>1</Id>
   <Marked name="ABC">100</Marked>
   <Total>650</Total>
</Item>
<Item>
   <Id>2</Id>
   <Total>10000</Total>
</Item>

I am not quite sure you need or want the inner grouping as I am not sure whether there can be different marks you want to distinguish but hopefully it gives you an idea.

If you simply want a total of Marked Items then I think

for $item in Base/Item
group by $id := $item/Id
order by $id
return <Item>
  <Id>{$id}</Id>
  {if ($item[Mark])
    then <MarkedQuantity>{sum($item[Mark]/Qty)}</MarkedQuantity>
    else ()
      }
  <Total>{sum($item/Qty)}</Total>
  </Item>

does that.

Thank you, but no, I need aggregate number of marked items, so I explicitly want to only get a total number of items that has Mark tag (wth any contents) Listing all marked items separately is what is happening now. I did found an easier solution myself meanwhile, so I will post it. — Gnudiff, Oct 16 '17 at 09:24

score 0 · Accepted Answer · answered Oct 16 '17 at 09:31

Turns out that the answer was way easier (as I suspected):

for $item in //Item
let $d := $item/Id
group by $d
order by $d
return  <Marked id="{$d}" Total="{ sum($item/Qty) }">{ sum($item[Mark]/Qty) }</Marked>

Basically, give 2 different sums for the same FOR clause: 1 with total sum of items, another with sum of filtered items (those that have Mark child tag in them).

I still do not understand though, why I can't use <Id>{ $d }</Id><Marked>{ sum($item[Mark]/Qty) }</Marked><Total>{ sum($item/Qty) }</Total> - in which case basex complains of finding } where it wanted >

But basically it works.

XQuery aggregate result AND give total

2 Answers2