Loading TOC...

op:group-by

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

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

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.
$keys 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.
$aggregates This parameter specifies either new columns for aggregate functions over the rows in the group or columndefs that are constant for the group. The aggregate library functions are listed below.

Usage Notes

The aggregate library functions for building the group-by parameters are as follows:

Example


(: Calculate the total expenses for each employee and return results 
   in order of employee number. :)

xquery version "1.0-ml";

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

let $employees := op:from-view("main", "employees")
let $expenses  := op:from-view("main", "expenses")
let $totalexpenses  := op:col("totalexpenses")
return $employees
   => op:join-inner($expenses, op:on(
                  op:view-col("employees", "EmployeeID"),
                  op:view-col("expenses", "EmployeeID")))
   => op:group-by(op:view-col("employees", "EmployeeID"),
                 ("FirstName", "LastName", 
                  op:view-col("expenses", "Category"),
                  op:sum($totalexpenses, 
                  op:view-col("expenses", "Amount"))))
   => op:order-by(op:view-col("employees", "EmployeeID")) 
   => op:result()

Example


(: Calculate the average expense across all expense reports. :)

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((), op:avg("Average Amount", "Amount")) 
   => op:result()

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