XQuery/XPath: How to get calculated value through group by function

Question

I will give an example to better explain myself:

<shop>

    <customers>
        <customer id="1">
            <level>A</level>
        </kunde>
        <customer id="2">
            <level>A</level>
        </kunde>
        <customer id="3">
            <level>B</level>
        </kunde>
        <customer id="4">
            <level>C</level>
        </kunde>
        <customer id="5">
            <level>C</level>
        </kunde>
         <customer id="6">
            <level>C</level>
        </kunde>
    </customers>


    <bills>
     <bill id="1">
            <customerId>1</customerId>
            <bposition>
                <price>1</price>
                <amount>1</amount>
            </bposition>
     </bill>
     <bill id="2">
            <customerId>2</customerId>
            <bposition>
                <price>2</price>
                <amount>2</amount>
            </bposition>
            <bposition>
                <price>3</price>
                <amount>2</amount>
            </bposition>

     </bill>

     <bill id ="3">
            <customerId>3</customerId>
            <bposition>
                <price>4</price>
                <amount>1</amount>
            </bposition>
     </bill>

     <bill id ="4">
            <customerId>1</customerId>
            <bposition>
                <price>2</price>
                <amount>2</amount>
            </bposition>
     </bill>

     <bill id ="5">
            <customerId>3</customerId>
            <bposition>
                <price>2</price>
                <amount>2</amount>
            </bposition>
            <bposition>
                <price>5</price>
                <amount>2</amount>
            </bposition>
     </bill>


     <bill id ="6">
            <customerId>4</customerId>
            <bposition>
                <price>1</price>
                <amount>3</amount>
            </bposition>
     </bill>

    <bill id ="7">
            <customerId>5</customerId>
            <bposition>
                <price>5</price>
                <amount>1</amount>
            </bposition>
     </bill>
     <bill id ="8">
            <customerId>6</customerId>
            <bposition>
                <price>2</price>
                <amount>1</amount>
            </bposition>
     </bill>

    </bills>

</shop>

I need to get the total revenue and revenue per customer grouped by their level. I probably need distinct-values to only get one level and sort them ascending like A, B, C.

Solution would be then:

<level val="A">
<totalRevenue>18</totalRevenue>
<revenueperCustomer>7.5</revenueperCustomer>
</level>

<level val="B">
<totalRevenue>18</totalRevenue>
<revenueperCustomer>18</revenueperCustomer>
</level>

<level val="C">
<totalRevenue>10</totalRevenue>
<revenueperCustomer>3.33</revenueperCustomer>
</level>

What I have so far:

let $s:= fn:doc('shop.xml')//customers/customer[level = 'A']/[@id],
$price := fn:doc('shop.xml')//bills/bill[customerId = $s]/bposition/price,
$amount := fn:doc('shop.xml')//bills/bill[customerId = $s]/bposition/amount

How can I properly group by level and sort it ? Like this

let $s:= fn:doc('shop.xml')//customers/customer/level
...
group by $s order by $s ascending

I don't know how to group the level and only select the right customer id so I can calculate it with the right values.

Answer 1

Here is a grouping example:

for $bill at $pos in /shop/bills/bill
group by $cat := /shop/customers/customer[@id = $bill/customerId]/level
order by head($pos)
return 
    <level val="{$cat}">{
        let $totalRevenue := sum($bill/bposition/(price * amount))
        return (
        <totalRevenue>{$totalRevenue}</totalRevenue>,
        <revenueperCustomer>{$totalRevenue div count(distinct-values($bill/customerId))}</revenueperCustomer>
        )
    }
    </level>

https://xqueryfiddle.liberty-development.net/pPqteB9

or with a file loaded with the doc function

declare context item := doc('shop.xml');

for $bill at $pos in /shop/bills/bill
group by $cat := /shop/customers/customer[@id = $bill/customerId]/level
order by head($pos)
return 
    <level val="{$cat}">{
        let $totalRevenue := sum($bill/bposition/(price * amount))
        return (
        <totalRevenue>{$totalRevenue}</totalRevenue>,
        <revenueperCustomer>{$totalRevenue div count(distinct-values($bill/customerId))}</revenueperCustomer>
        )
    }
    </level>

https://xqueryfiddle.liberty-development.net/pPqteB9/2