Aggregator Functions

These functions can be used on attributes when the GROUP BY construct is used in the TQL, functions are performed against all rows that fit into the group by attributes.

Function	Description
SUM	Sums all the values together
MIN	Picks the minimum value of the group
MAX	Picks the maximum value of the group
AVG	Calculates the average value of the group
STDDEVSAMP	Calculates the sample standard devation value of the group
STDDEVPOP	Calculates the population standard devation value of the group
COUNT	Counts the number of non-null values of the group
FIRST	Chooses the first value of the group
LAST	Chooses the last value of the group
ADDTOSET	Adds the values of the group to a set (no duplicates)
PUSH	Adds the values of the group to a list (may contain duplicates)

---

General Functions

SUM

Sums all the values together

SUM(<attribute>) AS <alias>

Paramter	Description
attribute	Array that contains the values to sum together
alias	The desired attribute name where you want to store the summed values

---

Examples

Starting Documents:

[{
  "item": 1
}, 
{
  "item": 2
},
{
  "item": 3
}]

GROUP BY portion for TQL or TQLFlow could look like this:

GROUP BY ALL 
    COMBINE
    SUM(item) AS item_sum

Resulting document:

{
  "item_sum": 6
}

---

Grouping by a specific attribute:

Starting Document:

[{
  "item_name" : "item_one", 
  "qty" : 2
},
{
  "item_name" : "item_two",
  "qty" : 5
},
{
  "item_name" : "item_one",
  "qty" : 6
}]

GROUP BY portion for TQLFlow could look like this:

GROUP BY item_name 
    COMBINE
    SUM(qty) AS qty_sum

Resulting document:

[{
  "item_name": "item_one",
  "qty_sum": 8
},
{
  "item_name": "item_two",
  "qty_sum": 5
}]

—

MIN

Picks the minimum value of the group

MIN(<attribute>) AS <alias>

Paramter	Description
attribute	The attribute you want to find the minimum value in.
alias	The desired attribute name where you want to store the minimum value

---

Examples

Starting Documents:

[{
  "item": 1
}, 
{
  "item": 2
},
{
  "item": 3
}]

GROUP BY portion for TQLFlow could look like this:

GROUP BY ALL
    COMBINE
    MIN(item) AS item_min

Resulting document:

{
  "item_min": 1
}

—

MAX

Picks the maximum value of the group

MAX(<attribute>) AS <alias>

Paramter	Description
attribute	The attribute you want to find the maximum value in.
alias	The desired attribute name where you want to store the maximum value

---

Examples

Starting Documents:

[{
  "item": 1
}, 
{
  "item": 2
},
{
  "item": 3
}]

GROUP BY portion for TQLFlow could look like this:

GROUP BY ALL 
    COMBINE
    MAX(item) AS item_max

Resulting document:

{
  "item_max": 3
}

—

AVG

Calculates the average value of the group

AVG(<attribute>) AS <alias>

Paramter	Description
attribute	The Attribute that contains the values you want to find the average of.
alisas	The desired attribute name where you want to store the avg value

---

Examples

Starting Documents:

[{
  "item": 1
}, 
{
  "item": 2
},
{
  "item": 3
}]

GROUP BY portion for TQLFlow could look like this:

GROUP BY
    AVG(item) AS item_avg

Resulting document:

{
  "item_avg": 2
}

—

STDDEVSAMP

Calculates the sample standard deviation of a value

STDDEVSAMP(<attribute>) AS <alias>

Paramter	Description
attribute	The attribute you want to get the sample standard deviation of

---

Examples

Starting Documents:

[{
  "item": 1
}, 
{
  "item": 10
},
{
  "item": 100
}]

GROUP BY portion for TQLFlow could look like this:

GROUP BY
    STDDEVSAMP(item) AS item_avg

Resulting document:

{
  "item_avg": 54.74486277268398
}

—

STDDEVPOP

Calculates the population standard deviation of a value

STDDEVPOP(<attribute>) AS <alias>

Paramter	Description
attribute	The attribute you want to get the population standard deviation of

---

Examples

Starting Documents:

[{
  "item": 1
}, 
{
  "item": 10
},
{
  "item": 100
}]

GROUP BY portion for TQLFlow could look like this:

GROUP BY
    STDDEVPOP(item) AS item_avg

Resulting document:

{
  "item_avg": 44.69899327725402
}

—

COUNT

Counts the number records in the grouping, or number of times specified attribute was present and not null in the grouuping.

COUNT <* | ALL> AS <alias> OR COUNT(<attribute-argument>) AS <alias>

Paramter	Description
alias	The desired attribute name where you want to store the count
attribute-argument	The attribute to count

---

Examples

Starting Documents:

[{
  "item": 1
}, 
{
  "item": 2
},
{
  "item": 3
}]

GROUP BY portion for TQLFlow could look like this:

GROUP BY ALL 
    COMBINE
    COUNT(item) AS item_count

Resulting document:

{
  "item_count": 3
}

—

FIRST

Chooses the first value of the group

FIRST(<attribute>) AS <alias>

Paramter	Description
attribute	The attribute to where you want the first value that is not null.
alias	The desired attribute name where you want to store the first value

Starting Documents:

[{
  "item": 1
}, 
{
  "item": 2
},
{
  "item": 3
}]

GROUP BY portion for TQLFlow could look like this:

GROUP BY ALL
    COMBINE
    FIRST(item) AS item_first

Resulting document:

{
  "item_first": 1
}

—

LAST

Chooses the last value of the group

LAST(<attribute>) AS <alias>

Paramter	Description
attribute	This will set the last value it finds in as the value for
alias	The desired attribute name where you want to store the last value

---

Examples

Starting Documents:

[{
  "item": 1
}, 
{
  "item": 2
},
{
  "item": 3
}]

GROUP BY portion for TQLFlow could look like this:

GROUP BY ALL 
    COMBINE
    LAST(item) AS item_last

Resulting document:

{
  "item_last": 3
}

—

ADDTOSET

Adds the values of the group to a set (no duplicates)

ADDTOSET(<attribute>) AS <alias>

Paramter	Description
attribute	The source for the values you want to add to the List
alias	The desired attribute name where you want to store the new list of values without duplicates

---

Examples

Starting Documents:

[{
  "item": 1
}, 
{
  "item": 2
},
{
  "item": 2
}]

GROUP BY portion for TQLFlow could look like this:

GROUP BY ALL 
    COMBINE
    ADDTOSET(item) AS item_added

Resulting document:

{
  "item_added": [1, 2]
}

—

PUSH

Adds the values of the group to a list (may contain duplicates)

PUSH(<attribute>) AS <alias>

Paramter	Description
attribute	Array that contains the values to sum together
alias	The desired attribute name where you want to store the list of attributes

---

Examples

Starting Documents:

[{
  "item": 1
}, 
{
  "item": 2
},
{
  "item": 2
}]

GROUP BY portion for TQLFlow could look like this:

GROUP BY
    PUSH(item) AS item_push

Resulting document:

{
  "item_push": [1, 2, 2]
}

—