group

info

This is part of our beta expression language AQL. Learn more. Request beta.

Definition

Group data by one or multiple dimensions. In AQL, the group function serves as an intermediate step for subsequent transformations. If used by itself, group will return a new table with all unique combination of values in the specified dimensions. To understand more about how group works, please refer to the Concepts page.

Syntax

group(table, dimension, dimension, ...)

Examples

group(products, products.category_id, products.name) // -> Table(products.category_id, products.name)

// with pipe
products | group(products.category_id, products.name) // -> Table(products.category_id, products.name)

Input

• table: A model reference or the returned table from a previous expression.
• dimension (repeatable): A fully-qualified reference to a dimension. The output table will have one row for each unique combination of values in the specified dimensions.

Output

A new table with one row for each unique combination of values in the specified dimensions.

Sample Usages

products | group(products.category_id, products.name) 

Open in AMQL playground

Result

SQL

SELECT
  "products"."category_id" AS "products->category_id",
  "products"."name" AS "products->name"
FROM
  "demo"."products" "products"
GROUP BY
  1,
  2

The special thing about group is that, it allow you to use Aggregation Functions in the context of a row of the grouped table. For example, if you want to create a table with the Total Product for each Product Category, you can use group and then use the sum function inside select to get the total product for each category:

Total product by product category

products | group(products.category_id) | select(products.category_id, count(products.id)) 

Open in AMQL playground

Result

SQL

SELECT
  "products"."category_id" AS "products->category_id",
  COUNT("products"."id") AS "count_products->id"
FROM
  "demo"."products" "products"
GROUP BY
  1

This is especially useful when you want to create a metric with nested aggregation. For example, getting the Average of the above table:

Average total product by product category

products 
| group(products.category_id) 
| select(count_product: count(products.id)) 
| avg(count_product)

Open in AMQL playground

Result

SQL

WITH "aql__t1" AS (
  SELECT
    "products"."category_id" AS "products->category_id",
    COUNT("products"."id") AS "count_product"
  FROM
    "demo"."products" "products"
  GROUP BY
    1
)
SELECT
  AVG("aql__t1"."count_product") AS "avg_count_product"
FROM
  "aql__t1"