Loading TOC...

op:group-by-union

op:group-by-union(
   $plan as map:map,
   $groups as item()+,
   [$aggregates as item()*]
) as map:map

Summary

This method performs the union of multiple group-by operations on a row set.

The aggregates for the operation are specified as the second parameter instead of in a op:select operation (unlike SQL).

The group-by-union operation provides a powerful, low-level capability. For many purposes, the higher-level op:group-to-arrays provides a convenient alternative.

Parameters
$plan The Optic Plan. You can either use the XQuery => chaining operator or specify the variable that captures the return value from the previous operation.
$groups The sets of grouping keys. The keys for each group are specified with the op:group, op:rollup, or op:cube functions. Each group must have a unique set of keys but multiple groups can have the same key.

As a convenience, a group with a single key can specify the name of the key column with a string or a column parameter function such as op:col or constructed from an expression with op:as.

The rows produced by the group-by-union operation include the key columns from each group. A group can be empty to group over all of the rows in the row set.

$aggregates This parameter specifies either columns to sample or aggregate functions to apply to a column for all of the rows in the group. Sampled columns can be existing columns or new columns created by an expression specified with op:as. Often a sampled column might have a constant value within the group such as a title or label closely associated with a numeric identifier used as the grouping key.

Usage Notes

The group-by-union operation differs from the group-by operation as follows:

See Also

Example

(: Calculate the total expenses separately for each category and for each location. :)

xquery version "1.0-ml";

import module namespace op="http://marklogic.com/optic"
     at "/MarkLogic/optic.xqy";

op:from-view("main", "expenses")
    => op:group-by-union(
        (op:group("Category"), op:group("Location"), op:group()),
        op:sum("TotalAmount", "Amount")
        )
    => op:order-by(("Category", "Location"))
    => op:result()
  

Stack Overflow iconStack Overflow: Get the most useful answers to questions from the MarkLogic community, or ask your own question.