Group By and Order By

group by $key := expr collapses the tuples sharing a key into one; afterwards every other variable holds the sequence of its values from the group, so count($o) counts orders and sum($revenue) totals them. order by sorts tuples on one or more keys, each ascending or descending, with empty greatest or empty least for missing keys, an optional collation, and stable to keep ties in input order. count $n numbers the tuples at its position in the clause list:

group.xq: revenue by genre, ranked

(: Revenue by genre: group the joined order lines, then rank the groups :)
for $o in doc("orders.xml")//order
let $b := //book[identifier = $o/@sku]
let $revenue := xs:decimal($b//price) * xs:integer($o/@qty)
group by $genre := string($b/subject)
let $total := sum($revenue)
order by $total descending, $genre
count $rank
return $rank || ". " || $genre || ": " || count($o) || " order(s), $"
       || format-number($total, "#,##0.00")

Running the grouped report

xquery -s:booknest-catalog.xml group.xq

Output

"1. Technology: 2 order(s), $118.50"
"2. Science Fiction: 2 order(s), $64.80"
"3. Fiction: 2 order(s), $44.97"
"4. Home and Garden: 1 order(s), $42.60"
"5. Travel: 1 order(s), $18.75"

This is SQL's GROUP BY ... ORDER BY ... ROW_NUMBER() over a join, written in one expression. Because count comes after order by, it ranks the sorted groups; placed before, it would number the input order. Multiply xs:decimal values for money: untyped values would fall back to doubles (Numeric and Math Functions).

MENU