SUM and GROUP BY in xquery with 1 xml file

Question

I have an SQL query:

SELECT ShipVia, SUM(Freight)
FROM Orders
GROUP BY ShipVia

which returns the values below from the access database

Ship Via  TotalFreight
   1       $16,185.33
   2       $28,244.85
   3       $20,512.51

I am trying to convert this to xquery (using xquery 1.0)

So far I have this,

xquery version "1.0";
for $x in doc("Orders.xml")/dataroot/Orders
return
<OrderDetails>
{
    $x/ShipVia,
    <TotalFreight>{sum($x/Freight)}</TotalFreight>
}
</OrderDetails>

This however, outputs every single order along with the freight cost as if I did

SELECT ShipVia, Freight
FROM Orders

in SQL

How do I make the xquery actually add each one up as the SQL command does

These are three orders from the Orders.xml file for example

<dataroot>
    <Orders>
        <ShipVia>1</ShipVia>
        <Freight>32.38</Freight>
    </Orders>
    <Orders>
        <ShipVia>1</ShipVia>
        <Freight>11.61</Freight>
    </Orders>
    <Orders>
        <ShipVia>2</ShipVia>
        <Freight>65.83</Freight>
    </Orders>
</dataroot>

EDIT: Stripped Unnecessary Nodes (Added Duplicate)

I think we have someone else doing the exact same homework also asking questions on StackOverflow. You might find it useful to look at some of the other recent XQuery-tagged questions. — Charles Duffy, Mar 13 '12 at 00:13
Yes, there seems to be, although looking at their question, it seems to be a lot more complex than mine — Daniel, Mar 13 '12 at 00:27
+1 for making your question simple and clear (which, yes, you've done better at). — Charles Duffy, Mar 13 '12 at 00:33
By the way, you should probably make sure your stripped-down test data includes at least one duplicated ShipVia value -- doesn't do to not test that the summing actually takes place. — Charles Duffy, Mar 13 '12 at 00:35

Charles Duffy · Accepted Answer · 2012-03-13T01:08:19.090

2

The following is one approach to doing this in XQuery 1.0 (which lacks XQuery 3.0's native group by operator):

let $doc := 
    <dataroot>
        <Orders>
            <ShipVia>1</ShipVia>
            <Freight>32.38</Freight>
        </Orders>
        <Orders>
            <ShipVia>1</ShipVia>
            <Freight>11.61</Freight>
        </Orders>
        <Orders>
            <ShipVia>2</ShipVia>
            <Freight>65.83</Freight>
        </Orders>
    </dataroot>

let $ship_via_values := distinct-values($doc/Orders/ShipVia/text())
for $ship_via_value in $ship_via_values
return
  <OrderDetails>
    <ShipVia>{$ship_via_value}</ShipVia>
    <TotalFreight>{
      sum($doc/Orders[ShipVia=$ship_via_value]/Freight)
    }</TotalFreight>
  </OrderDetails>

This query returns the following result:

<OrderDetails>
  <ShipVia>1</ShipVia>
  <TotalFreight>43.99</TotalFreight>
</OrderDetails>
<OrderDetails>
  <ShipVia>2</ShipVia>
  <TotalFreight>65.83</TotalFreight>
</OrderDetails>

...which appears to be desired output.

edited Mar 13 '12 at 01:08

answered Mar 13 '12 at 00:32

Charles Duffy

280,126
43
390
441

Is there a way to do this in xquery 1.0? or is it a lot more complicated to achieve? – Daniel Mar 13 '12 at 00:53
@Daniel that _was_ the XQuery 1.0 approach. I'm rewording the aside about XQuery 3.0, as it clearly made the answer confusing. – Charles Duffy Mar 13 '12 at 01:07
Just tried the answer you provided and it works perfectly when the xml data is within the query file as you have written it How would I incorporate this to work referencing the file externally like my original question? – Daniel Mar 13 '12 at 01:23
@Daniel if you wanted to make no other changes whatsoever, `let $doc := doc("Orders.xml")` at the beginning. – Charles Duffy Mar 13 '12 at 02:29
Unfortunately this does not work, I get an empty result when the query in executed – Daniel Mar 13 '12 at 02:56
1

`let $doc := doc("Orders.xml")/dataroot`, rather. – Charles Duffy Mar 13 '12 at 03:51

SUM and GROUP BY in xquery with 1 xml file

1 Answers1

Linked