MarkLogic 9 Product Documentation
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()

MarkLogic 9 Product Documentationop:group-by

Summary

Usage Notes

Example

Example

MarkLogic 9 Product Documentation
op:group-by