Loading TOC...

ModifyPlan.prototype.groupByUnion

ModifyPlan.prototype.groupByUnion(
   $groups as groupdef[],
   [$aggregates as aggregatedef[]]
) as ModifyPlan

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 prototype.select operation (unlike SQL).

The group-by-union operation provides a powerful, low-level capability. For many purposes, the higher-level prototype.groupToArrays provides a convenient alternative.

Parameters
$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 groupByUnion operation differs from the groupBy operation as follows:

groupByUnion is a method of the following classes:

See Also

Example

// Calculate the total expenses separately for each category and for each location.

const op = require('/MarkLogic/optic');

op.fromView('main', 'expenses')
   .groupByUnion(
       [op.group('Category'), op.group('Location'), op.group()],
       op.sum("TotalAmount", "Amount")
       )
   .orderBy(['Category', 'Location'])
   .result();
  

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