Sum with group by xquery

Question

I am trying to convert the following sql to

SELECT SUM((((1-Discount)*Quantity)*[Unit Price]))  AS AMOUNT 
FROM Orders 
LEFT JOIN [Order Details] 
    ON Orders.[Order ID] = [Order Details].[Order ID] 
WHERE ((([Order Date]) BETWEEN #1/1/2006# AND #2/1/2006#));

The orders and order details have a 1 to many relationship (1 is with orders)

The code I currently have displays the full price for each order details but however wont allow me to combine each total value of the order details together to get the full value.

for $orderdetails in doc("Order Details.xml")/dataroot/Order_x0020_Details
let $order := doc("Orders.xml")/dataroot/Orders[OrderID = $orderdetails/OrderID]

where xs:dateTime($order/OrderDate/text()) gt xs:dateTime("1996-04-06T00:00:00") and  xs:dateTime($order/OrderDate/text()) lt xs:dateTime("1997-04-05T00:00:00")

return 
<Invoice_Amounts>
{
    $order/OrderID
}
<Amount>
{
 sum(((1 - xs:double($orderdetails/Discount)) * xs:double($orderdetails/UnitPrice)) * xs:double($orderdetails/Quantity))
}
</Amount>
</Invoice_Amounts>

Sample of Orders.xml

<OrderID>10248</OrderID>
        <CustomerID>WILMK</CustomerID>
        <EmployeeID>5</EmployeeID>
        <OrderDate>1996-07-04T00:00:00</OrderDate>
        <RequiredDate>1996-08-01T00:00:00</RequiredDate>
        <ShippedDate>1996-07-16T00:00:00</ShippedDate>
        <ShipVia>3</ShipVia>
        <Freight>32.38</Freight>
        <ShipName>Vins et alcools Chevalier</ShipName>
        <ShipAddress>59 rue de l&apos;Abbaye</ShipAddress>
        <ShipCity>Reims</ShipCity>
        <ShipPostalCode>51100</ShipPostalCode>
        <ShipCountry>France</ShipCountry>
    </Orders>
    <Orders>
        <OrderID>10249</OrderID>
        <CustomerID>TRADH</CustomerID>
        <EmployeeID>6</EmployeeID>
        <OrderDate>1996-07-05T00:00:00</OrderDate>
        <RequiredDate>1996-08-16T00:00:00</RequiredDate>
        <ShippedDate>1996-07-10T00:00:00</ShippedDate>
        <ShipVia>1</ShipVia>
        <Freight>11.61</Freight>
        <ShipName>Toms Spezialitäten</ShipName>
        <ShipAddress>Luisenstr. 48</ShipAddress>
        <ShipCity>Münster</ShipCity>
        <ShipPostalCode>44087</ShipPostalCode>
        <ShipCountry>Germany</ShipCountry>
    </Orders>

Sample of `Order Details.xml

    <OrderID>10248</OrderID>
<ProductID>11</ProductID>
<UnitPrice>14</UnitPrice>
<Quantity>12</Quantity>
<Discount>0</Discount>
</Order_x0020_Details>
<Order_x0020_Details>
<OrderID>10248</OrderID>
<ProductID>42</ProductID>
<UnitPrice>9.8</UnitPrice>
<Quantity>10</Quantity>
<Discount>0</Discount>
</Order_x0020_Details>
<Order_x0020_Details>
<OrderID>10248</OrderID>
<ProductID>72</ProductID>
<UnitPrice>34.8</UnitPrice>
<Quantity>5</Quantity>
<Discount>0</Discount>
</Order_x0020_Details>
<Order_x0020_Details>
<OrderID>10249</OrderID>
<ProductID>14</ProductID>
<UnitPrice>18.6</UnitPrice>
<Quantity>9</Quantity>
<Discount>0</Discount>
</Order_x0020_Details>

Answer 1

So -- as I understand it, you already know how to do the join, so the thing to focus on is the sum operation. The easiest way to do this is by using a for loop to set a variable, which you can then use:

let $doc :=
    <docroot>
        <Order id="1">
            <Discount>0.05</Discount>
            <Quantity>3</Quantity>
            <UnitPrice>15</UnitPrice>
        </Order>
        <Order id="2">
            <Discount>0</Discount>
            <Quantity>15</Quantity>
            <UnitPrice>20</UnitPrice>
        </Order>
    </docroot>

(: collect the orders relevant to your query;
   in your "real" version you'll want to filter,
   or do your join, or whatever :)
let $orders := $doc/Order

(: generate a sequence containing the price for
   each such order :)
let $order_costs := 
    for $order in $orders
        return ((1.0 - xs:double($order/Discount))
                    * xs:double($order/UnitPrice)
                    * xs:double($order/Quantity))

(: generate your final result :)
return sum($order_costs)

You also could avoid a variable assignment by embedding the for loop within the argument to sum():

return sum(for $order in $orders
           return ((1.0 - xs:double($order/Discount))
                    * xs:double($order/UnitPrice)
                    * xs:double($order/Quantity))