CODEWITHSUNDEEP
sqlgroup-byxquery
Peter Roche
Peter Roche

Reputation: 335

Group By in xquery

I am trying to convert the following code to xquery so that the results remain the same. I am trying to calculate the total freight costs for each customer

SELECT 
    c.CompanyName, SUM(o.freight) AS [Total Freight Costs] 
FROM 
    Customers c, Orders o 
WHERE 
    c.CustomerID = o.CustomerID  
GROUP BY 
    CompanyName

The test data for the customers.xml and orders are as follows

<dataroot>
  <Customers>
    <CustomerID>ALFKI</CustomerID>
    <CompanyName>Alfreds Futterkiste</CompanyName>
    <ContactName>Maria Anders</ContactName>
    <ContactTitle>Sales Representative</ContactTitle>
    <Address>Obere Str. 57</Address>
    <City>Berlin</City>
    <PostalCode>12209</PostalCode>
    <Country>Germany</Country>
    <Phone>030-0074321</Phone>
    <Fax>030-0076545</Fax>
  </Customers>
  <Customers>
    <CustomerID>ANATR</CustomerID>
    <CompanyName>Ana Trujillo Emparedados y helados</CompanyName>
    <ContactName>Ana Trujillo</ContactName>
    <ContactTitle>Owner</ContactTitle>
    <Address>Avda. de la Constitución 2222</Address>
    <City>México D.F.</City>
    <PostalCode>05021</PostalCode>
    <Country>Mexico</Country>
    <Phone>(5) 555-4729</Phone>
    <Fax>(5) 555-3745</Fax>
  </Customers>
</dataroot>

Orders.xml

<dataroot>
  <Orders>
    <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>
</dataroot>

This is what I have so far 

for $o in doc("Orders.xml")/dataroot/Orders,
    $c in doc("Customers.xml")/dataroot/Customers[CustomerID = $o/CustomerID] 

return
<OrderDetails>
{
    $c/CompanyName,
    sum($o/Freight)
}
</OrderDetails>

Upvotes: 1

Views: 1706

Answers (2)

mb21
mb21

Reputation: 39209

Actually, since XQuery 3.0 there is a standardized "group by" for FLWOR expressions.

Upvotes: 0

Charles Duffy
Charles Duffy

Reputation: 295403

Simple enough:

(: select only customers with at least one order :)
for $c in doc("Customers.xml")/dataroot/Customers
  [doc("Orders.xml")/dataroot/Orders/CustomerID=./CustomerID]

(: get the list of orders for this customer :)
let $o := doc("Orders.xml")/dataroot/Orders[CustomerID = $c/CustomerID] 

(: ...and properly encapsulate the calculated value in the result :)
return
  <OrderDetails>
    {$c/CompanyName}
    <TotalFreight>{sum($o/Freight)}</TotalFreight>
  </OrderDetails>

The test data you gave has no matching CustomerIDs, however, so it returns nothing for this query; I had to mock up my own. With that done, however, I get output akin to the following:

<OrderDetails>
  <CompanyName>Alfreds Futterkiste</CompanyName>
  <TotalFreight>45.58</TotalFreight>
</OrderDetails>

Upvotes: 3

Related Questions