MarkLogic 9 Product DocumentationAccessPlan.prototype.groupBy
AccessPlan.prototype.groupBy( groupdef as String, [aggregatedef as String] ) as ModifyPlan
Summary
This method collapses a group of rows into a single row.
If you want the results to include a column, specify the column either as a grouping key or as one of the aggregates. A group-by operation without a grouping key outputs a single group reflecting the entire row set.
The aggregates for a group by operation are specified as the second parameter instead of in a prototype.select operation (unlike SQL).
| Parameters | |
|---|---|
| groupdef | This parameter specifies the columns used to determine the groups. Rows with the same values in these columns are consolidated into a single group. The columns can be existing columns or new columns created by an expression specified with op.as. The rows produced by the group by operation include the key columns. Specify an empty sequence to create a single group for all of the rows in the row set. |
| aggregatedef | 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. The aggregate functions are listed below. |
Usage Notes
groupBy is a method of the following classes:
The aggregate library functions for building the groupBy parameters are as follows:
Example
// Calculate the total expenses for each employee and return results in
// order of employee number.
const op = require('/MarkLogic/optic');
const employees = op.fromView('main', 'employees');
const expenses = op.fromView('main', 'expenses');
const totalexpenses = op.col('totalexpenses');
const Plan =
employees.joinInner(expenses, op.on(employees.col('EmployeeID'), expenses.col('EmployeeID')))
.groupBy(employees.col('EmployeeID'), ['FirstName', 'LastName', expenses.col('Category'),
op.sum(totalexpenses, expenses.col('Amount'))])
.orderBy('EmployeeID')
Plan.result();
Example
// Calculate the average expense across all expense reports.
const op = require('/MarkLogic/optic');
op.fromView('main', 'expenses')
.groupBy(null, op.avg("Average Amount", "Amount"))
.result();